How to Insert Date in MySQL (With Example)


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:

MySQL: How to Add Column After a Specific Column
MySQL: How to Add Column in First Position
MySQL: How to Add Auto-Increment Column to Existing Table

Leave a Reply

Your email address will not be published. Required fields are marked *