How Do I Insert Date in YYYY-MM-DD Format in MySQL?

There are times when the user prefers to have date values stored inside the MySQL database in a specific format. But doing this might prove to be a challenge for many people. This task is even considered to be quite difficult to arrange the date values in a specific order. However, that is not the case.

This post will demonstrate how to insert a date in the “YYYY-MM-DD” format in MySQL through different methods.

Note: Ensure that you have logged in to the MySQL server using Command Prompt and used the specific database to continue further.

Insert the Date as a String

To insert the string as a date value in the table along with another value, run this code:

INSERT INTO time (id, time) VALUES (3, '2023-01-05');

And then, to see the data of the table, type this command:

select * from time;

Note: The “time” is the name of the table. And this command will be used throughout the post to see the applied changes on the table.

The table contains the inserted date value in the format “YYYY-MM-DD”:

Insert the Date Using STR_TO_DATE()

Convert a string into the “DATE” data type and format it using the “STR_TO_DATE()” function of MySQL, use the following command:

INSERT INTO time (id, time) VALUES (4, STR_TO_DATE('2023-01-20', '%Y-%m-%d'));

SELECT * FROM time;

The value is inserted as the “DATE” data type in the table successfully:

Insert the Date Using DATE()

To insert a value in the “DATE” data type, use the “DATE()” function which formats the value “YYYY-MM-DD” by default using the following commands::

INSERT INTO time (id, time) VALUES (5, DATE('2023-02-02'));

SELECT * FROM time;

The output displays the “DATE” value inserted in the table:

Insert the Date Using NOW()

The “NOW()” function is a built-in function of MySQL that returns the current date and time, so you can use it inside the “DATE()” function to format the current date of the system and insert in the table in the “DATE” data type. Run the commands given below:

INSERT INTO time (id, time) VALUES (6, DATE(NOW()));

SELECT * FROM time;

The output displays the table with the result of the query:

Insert the Date Using CURDATE()

MySQL also provides a built-in function “CURDATE()” to return the current date in the “YYYY-MM-DD” by default. Use this in the “INSERT” statement

INSERT INTO time (id, time) VALUES (7, CURDATE());

SELECT * FROM time;

The output displays the DATE value has been inserted in the table with YYYY-MM-DD format::

Insert the Date Using DATE_FORMAT()

The DATE_FORMAT() also provides the functionality to format the date in YYYY-MM-DD that can be inserted into the MySQL table, as it takes two arguments. One is the date value, and the other one is for the formatting. So to insert the date in the YYYY-MM-DD format using DATE_FORMAT(), use this code:

INSERT INTO time (id, time) VALUES (8, DATE_FORMAT(CURDATE(), '%Y/%m/%d'));

In this example, the value is CURDATE(), and the format is “%Y%m%d” or YYYY-MM-DD.

In the output, it is clearly displayed that the date has been inserted in YYYY-MM-DD format in the time table:

Conclusion

It’s very good practice to insert any value in the proper format in MySQL databases. In the case of storing date values, the preferred format is YYYY-MM-DD, and this post is all about the demonstration of inserting date values in that specific format. It can be inserted using STR_TO_DATE(), DATE(), NOW(), CURDATE(), and DATE_FORMAT(), which has been demonstrated along with examples.