Summary: in this tutorial, you will learn how to insert date values into a table in the MySQL database.
The DATE
data type allows you to store the date values. The following shows how to define a column with the DATE
data type:
column_name DATE
Code language: SQL (Structured Query Language) (sql)
To insert a date value into a column with the DATE
data type, you use the following date format:
'YYYY-MM-DD'
Code language: SQL (Structured Query Language) (sql)
In this format:
YYYY
is four digits that represent the year e.g., 2023MM
is two digits that represent the month e.g., 10DD
is two digits that represent the day e.g., 30.
The valid range for the DATE
values is '1000-01-01'
to '999-12-31'
. If you attempt to insert an invalid date value into a date column, the
We’ll take some examples of inserting date values into the DATE
column.
Inserting a date value into a date column
First, create a table called sales_orders
:
CREATE TABLE events(
id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255) NOT NULL,
event_date DATE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
The events
table has three columns:
id
is the auto-increment primary key column that uniquely identifies each event.event_name
column stores the name of the event.event_date
column stores the date of the event.
Second, insert a new row into the events
table:
INSERT INTO events(event_name, event_date)
VALUES('MySQL Conference','2023-10-29');
Code language: SQL (Structured Query Language) (sql)
In this statement, we insert a new event into the events
table with the event date of '2023-10-29'
.
Third, query data from the events
table:
SELECT event_name, event_date
FROM events;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------------+------------+
| event_name | event_date |
+------------------+------------+
| MySQL Conference | 2023-10-29 |
+------------------+------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The output shows that the mysql client displays the date value as ‘YYYY
–MM
–DD
‘.
If you attempt to insert an invalid date value into a DATE
column, MySQL will issue an error. For example:
INSERT INTO events(event_name, event_date)
VALUES('MySQL Database Design Workshop','2023-10-39');
Code language: SQL (Structured Query Language) (sql)
Error:
ERROR 1292 (22007): Incorrect date value: '2023-10-39' for column 'event_date' at row 1
Code language: SQL (Structured Query Language) (sql)
In this example, we attempt to insert an invalid date value '2023-10-39'
into the event_date
column, resulting in an error.
Inserting the current date
To insert the current date of the database server into a date column, you use the CURRENT_DATE
function. For example:
INSERT INTO events(event_name, event_date)
VALUES('MySQL Replication Workshop', CURRENT_DATE);
Code language: SQL (Structured Query Language) (sql)
In this example, the CURRENT_DATE
function returns the current date of the MySQL database server, which is then used in the INSERT
statement to insert the current date value into the events table.
To insert the current UTC date into a date column, you use the UTC_DATE()
function to get the current date in UTC and then insert it. For example:
INSERT INTO events(event_name, event_date)
VALUES('MySQL Day in USA', UTC_DATE());
Code language: SQL (Structured Query Language) (sql)
Inserting a date string example
If you have a date string that is not in the 'YYYY-MM-DD'
format and you want to insert it into a date column, you can use the STR_TO_DATE()
function to convert it into a date first. For example:
INSERT INTO events (event_name, event_date)
VALUES ('MySQL Innovate', STR_TO_DATE('10/29/2023', '%m/%d/%Y'));
Code language: SQL (Structured Query Language) (sql)
In this example, the date value is in the format MM/DD/YYYY
, which is not what MySQL expects.
Therefore, we use the STR_TO_DATE()
function to convert that string into a date value and insert it into the date column.
Summary
- Use the date value in the format
'YYYY-MM-DD'
when inserting it into a date column. - Use the
CURRENT_DATE
to insert the current date from the MySQL database server into a date column. - Use the
UTC_DATE()
function to insert the current date in UTC into a date column. - Use the
STR_TO_DATE()
function to convert a date string into a date before inserting it into a date column.