I have to insert dates in MySQL with the format dd-mm-yy
.
I know that if I change the date format to yy-mm-dd
it will work, but my client wants it in the format dd-mm-yy
, and the default format of a MySQL date is 0000-00-00
.
asked Feb 23, 2018 at 12:04
Ali Al AmineAli Al Amine
1,4553 gold badges32 silver badges50 bronze badges
1
As Honeyboy said in the comments, you can use the SELECT_TO_DATE
function with the format string "%d-%m-%y"
. You can test it like this:
CREATE DATABASE test;
USE TEST;
CREATE TABLE test [date1 date];
INSERT INTO test [date1] VALUES [STR_TO_DATE["03-04-15","%d-%m-%y"]];
then with
SELECT * FROM test;
you obtain:
+------------+
| date1 |
+------------+
| 2015-04-03 |
+------------+
If you use PHP, you can then make a prepared statement such as:
$stmt = $conn->prepare["INSERT INTO test [date1] VALUES [STR_TO_DATE[?,'%d-%m-%y']];"];
$stmt->bind_param["s", $client_date];
$stmt->execute[];
Same principle with other languages.
answered Feb 26, 2018 at 19:37
Ortomala LokniOrtomala Lokni
51.1k17 gold badges168 silver badges214 bronze badges
4
For this, use STR_TO_DATE[]. Following is the syntax −
insert into yourTableName values[STR_TO_DATE[yourDateValue,yourFormatSpecifier]];
Let us first create a table −
mysql> create table DemoTable [ ShippingDate date ]; Query OK, 0 rows affected [0.81 sec]
Insert some records in the table using insert command : Here, we are inserting formatted dates using date formats like m, d, y, etc −
mysql> insert into DemoTable values[STR_TO_DATE['06-01-2019', '%m-%d-%Y']]; Query OK, 1 row affected [0.22 sec] mysql> insert into DemoTable values[STR_TO_DATE['01-31-2019', '%m-%d-%Y']]; Query OK, 1 row affected [0.22 sec] mysql> insert into DemoTable values[STR_TO_DATE['02-01-2018', '%m-%d-%Y']]; Query OK, 1 row affected [0.27 sec]
Display all records from the table using select statement −
mysql> select *from DemoTable;
Output
+--------------+ | ShippingDate | +--------------+ | 2019-06-01 | | 2019-01-31 | | 2018-02-01 | +--------------+ 3 rows in set [0.00 sec]
Updated on 30-Jul-2019 22:30:26
- Related Questions & Answers
- MySQL query to convert YYYY-MM-DD to DD Month, YYYY date format
- Convert MySQL date format from yyyy-mm-ddThh:mm:ss.sssZ to yyyy-mm-dd hh:mm:ss ?
- MySQL date format DD/MM/YYYY select query?
- How to format JavaScript date into yyyy-mm-dd format?
- How to parse Date from String in the format: dd/MM/yyyy to dd/MM/yyyy in java?
- MySQL date format to convert dd.mm.yy to YYYY-MM-DD?
- Get date format DD/MM/YYYY with MySQL Select Query?
- How to convert MM/YY to YYYY-MM-DD in MYSQL?
- Java Program to format date in mm-dd-yyyy hh:mm:ss format
- MySQL - Convert YYYY-MM-DD to UNIX timestamp
- Get today's date in [YYYY-MM-DD] format in MySQL?
- Program to reformat date in YYYY-MM-DD format using Python
- Accepting date strings [MM-dd-yyyy format] using Java regex?
- Change date format [in DB or output] to dd/mm/yyyy in PHP MySQL?
- Convert dd/mm/yyyy string to Unix timestamp in MySQL?
Summary: in this tutorial, we will introduce you to the MySQL DATE
data type and show you some useful date functions to handle the date data effectively.
Introduction to MySQL DATE data type
MySQL DATE
is one of the five temporal data types used for managing date values. MySQL uses yyyy-mm-dd
format for storing a date
value. This format is fixed and it is not possible to change it.
For example, you may prefer to use mm-dd-yyyy
format but you can’t. Instead, you follow the standard date format and use the DATE_FORMAT
function to format the date the way you want.
MySQL uses 3 bytes to store a DATE
value. The DATE
values range from 1000-01-01
to 9999-12-31
. If you want to store a date value that is out of this range, you need to use a non-temporal data type like
integer e.g., three columns, and each column for the year, month, and day. You also need to create stored functions to simulate the built-in date functions provided by MySQL, which is not recommended.
When strict mode is disabled, MySQL converts any invalid date e.g., 2015-02-30
to the zero date value 0000-00-00
.
MySQL Date values with two-digit years
MySQL stores the year of the date value using four digits. In case you use two-digit year values, MySQL still accepts them with the following rules:
- Year values in the range 00-69 are converted to 2000-2069.
- Year values in the range 70-99 are converted to 1970 – 1999.
However, a date value with two digits is ambiguous therefore you should avoid using it.
Let’s take a look at the following example.
First,
create a table named people with birth date column with DATE
data type.
Code language: SQL [Structured Query Language] [sql]
CREATE TABLE people [ id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR[50] NOT NULL, last_name VARCHAR[50] NOT NULL, birth_date DATE NOT NULL ];
Next, insert a row into the people
table.
Code language: SQL [Structured Query Language] [sql]
INSERT INTO people[first_name,last_name,birth_date] VALUES['John','Doe','1990-09-01'];
Then, query the data from the people
table.
Code language: SQL [Structured Query Language] [sql]
SELECT first_name, last_name, birth_date FROM people;
After that, use the two-digit year format to insert data into the people
table.
Code language: SQL [Structured Query Language] [sql]
INSERT INTO people[first_name,last_name,birth_date] VALUES['Jack','Daniel','01-09-01'], ['Lily','Bush','80-09-01'];
In the first row, we used 01 [range 00-69] as the year, so MySQL converted it to 2001. In the second row, we used 80 [range 70-99] as the year, MySQL converted it to 1980.
Finally, we can query data from the people
table to check whether data was converted based on the conversion rules.
Code language: SQL [Structured Query Language] [sql]
SELECT first_name, last_name, birth_date FROM people;
MySQL provides many useful date functions that allow you to manipulate date effectively.
To get the current date and time, you
use NOW[]
function.
Code language: SQL [Structured Query Language] [sql]
SELECT NOW[];
Code language: SQL [Structured Query Language] [sql]
+---------------------+ | NOW[] | +---------------------+ | 2017-05-13 07:59:38 | +---------------------+ 1 row in set [0.02 sec]
To get only date part of a DATETIME
value, you use the DATE[]
function.
Code language: SQL [Structured Query Language] [sql]
SELECT DATE[NOW[]];
Code language: SQL [Structured Query Language] [sql]
+-------------+ | DATE[NOW[]] | +-------------+ | 2015-07-13 | +-------------+ 1 row in set [0.01 sec]
To get the current system date, you use CURDATE[]
function as follows:
Code language: SQL [Structured Query Language] [sql]
SELECT CURDATE[];
Code language: SQL [Structured Query Language] [sql]
+------------+ | CURDATE[] | +------------+ | 2015-07-13 | +------------+ 1 row in set [0.02 sec]
To format a date value, you use DATE_FORMAT
function. The following statement formats the date asmm/dd/yyyy
using the date format pattern %m/%d/%Y
:
Code language: SQL [Structured Query Language] [sql]
SELECT DATE_FORMAT[CURDATE[], '%m/%d/%Y'] today;
Code language: SQL [Structured Query Language] [sql]
+------------+ | today | +------------+ | 07/13/2015 | +------------+ 1 row in set [0.02 sec]
To calculate the number of days between two date values, you use the DATEDIFF
function as follows:
Code language: SQL [Structured Query Language] [sql]
SELECT DATEDIFF['2015-11-04','2014-11-04'] days;
Code language: SQL [Structured Query Language] [sql]
+------+ | days | +------+ | 365 | +------+ 1 row in set [0.02 sec]
To add a number of days, weeks, months, years, etc., to a date value, you use the DATE_ADD
function:
Code language: SQL [Structured Query Language] [sql]
SELECT '2015-01-01' start, DATE_ADD['2015-01-01', INTERVAL 1 DAY] 'one day later', DATE_ADD['2015-01-01', INTERVAL 1 WEEK] 'one week later', DATE_ADD['2015-01-01', INTERVAL 1 MONTH] 'one month later', DATE_ADD['2015-01-01', INTERVAL 1 YEAR] 'one year later';
Similarly, you can subtract an interval from a date using the DATE_SUB
function:
Code language: SQL [Structured Query Language] [sql]
SELECT '2015-01-01' start, DATE_SUB['2015-01-01', INTERVAL 1 DAY] 'one day before', DATE_SUB['2015-01-01', INTERVAL 1 WEEK] 'one week before', DATE_SUB['2015-01-01', INTERVAL 1 MONTH] 'one month before', DATE_SUB['2015-01-01', INTERVAL 1 YEAR] 'one year before';
If you want to get the day, month, quarter, and year of a date
value, you can use the corresponding function DAY
, MONTH
, QUARTER
, and YEAR
as follows:
Code language: SQL [Structured Query Language] [sql]
SELECT DAY['2000-12-31'] day, MONTH['2000-12-31'] month, QUARTER['2000-12-31'] quarter, YEAR['2000-12-31'] year;
Code language: SQL [Structured Query Language] [sql]
+------+-------+---------+------+ | day | month | quarter | year | +------+-------+---------+------+ | 31 | 12 | 4 | 2000 | +------+-------+---------+------+ 1 row in set [0.00 sec]
To get the week information week related functions. For example, WEEK
function returns
the week number, WEEKDAY
function returns the weekday index, and WEEKOFYEAR
function returns the calendar week.
Code language: SQL [Structured Query Language] [sql]
SELECT WEEKDAY['2000-12-31'] weekday, WEEK['2000-12-31'] week, WEEKOFYEAR['2000-12-31'] weekofyear;
Code language: SQL [Structured Query Language] [sql]
+---------+------+------------+ | weekday | week | weekofyear | +---------+------+------------+ | 6 | 53 | 52 | +---------+------+------------+ 1 row in set [0.04 sec]
The week function returns the week number with the zero-based index if you don’t pass the second argument or if you pass 0. If you pass 1, it will return week number with 1-indexed.
Code language: SQL [Structured Query Language] [sql]
SELECT WEEKDAY['2000-12-31'] weekday, WEEK['2000-12-31',1] week, WEEKOFYEAR['2000-12-31'] weekofyear;
Code language: SQL [Structured Query Language] [sql]
+---------+------+------------+ | weekday | week | weekofyear | +---------+------+------------+ | 6 | 52 | 52 | +---------+------+------------+ 1 row in set [0.00 sec]
In this tutorial, you have learned about the MySQL DATE
data type and how to use some useful date functions to manipulate date values.
Was this tutorial helpful?