6. INSERT DATE

– learn how to insert date values into a DATE column.

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 DATECode 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., 2023
• MM is two digits that represent the month e.g., 10
• DD 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 1Code 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.