MySQL Dates

MySQL Dates

The most difficult part when working with dates in databases, is to be sure that the format of the date you are trying to insert/select, matches the format of the date column in the database.

MySQL Date Data Types

MySQL has the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• TIME - format: HH:MI:SS
• YEAR - format YYYY or YY

Note: The date datatype are defined for a column upon creation of a new table in your database.

MySQL Working with Dates

Look at the following table:

Orders Table

Now we want to select the records with an OrderDate of "2025-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2025-11-11'

The result-set will look like this:

Note: Two dates can easily be compared if there is no time component involved!

Now, assume that the "Orders" table looks like this (notice the added time-component in the "OrderDate" column):

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2025-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.

Tip: To keep your queries simple and easy to maintain, do not use time-components in your dates, unless you have to!