Table of Contents
To insert a date in MySQL, you can use the INSERT command along with the DATE function. For example, if you want to insert the current date into a table called “orders”, the command would be: INSERT INTO orders (date) VALUES (DATE(NOW())). This will insert the current date into the “date” column of the “orders” table. Alternatively, you can specify a specific date in the VALUES section instead of using the DATE function.
You can use DATE to insert a date column into a table in MySQL.
When inserting dates, they must be in the following format:
'YYYY-MM-DD' where:
- YYYY: The year in four digits
- MM: The month in two digits
- DD: The day in two digits
The following example shows how to insert a date column into a table in MySQL in practice.
Example: How to Insert Date in MySQL
We can use the following syntax to create a table named athletes that contains information about various basketball players:
-- create table
CREATE TABLE athletes (
athleteID INT PRIMARY KEY,
team TEXT NOT NULL,
join_date DATE NOT NULL
);
-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', '2015-01-12');
INSERT INTO athletes VALUES (0002, 'Warriors', '2020-11-25');
INSERT INTO athletes VALUES (0003, 'Nuggets', '2009-06-30');
INSERT INTO athletes VALUES (0004, 'Lakers', '2022-04-09');
INSERT INTO athletes VALUES (0005, 'Celtics', '2023-05-19');
-- view all rows in table
SELECT * FROM athletes;
Output:
+-----------+----------+------------+
| athleteID | team | join_date |
+-----------+----------+------------+
| 1 | Mavs | 2015-01-12 |
| 2 | Warriors | 2020-11-25 |
| 3 | Nuggets | 2009-06-30 |
| 4 | Lakers | 2022-04-09 |
| 5 | Celtics | 2023-05-19 |
+-----------+----------+------------+ Notice that the join_date column is a date column where each of the dates are formatted as YYYY-MM-DD.
If you attempt to insert a date in a format other than YYYY-MM-DD, you will receive an error:
-- insert row into table
INSERT INTO athletes VALUES (0001, 'Mavs', '5/19/2023');
-- view all rows in table
SELECT * FROM athletes; Output:
ERROR 1292 (22007): Incorrect date value: '5/19/2023' for column 'join_date' at row 1
If you would like to insert a date into the date column in a different format, you can use STR_TO_DATE to do so:
-- insert row into table
INSERT INTO athletes VALUES (0006, 'Cavs', STR_TO_DATE('10/31/2023', '%m/%d/%Y'));
-- view all rows in table
SELECT * FROM athletes; Output:
+-----------+----------+------------+
| athleteID | team | join_date |
+-----------+----------+------------+
| 1 | Mavs | 2015-01-12 |
| 2 | Warriors | 2020-11-25 |
| 3 | Nuggets | 2009-06-30 |
| 4 | Lakers | 2022-04-09 |
| 5 | Celtics | 2023-05-19 |
| 6 | Cavs | 2023-10-31 |
+-----------+----------+------------+ By using STR_TO_DATE, we are able to insert this new date value without any errors.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: