MySQL Dates

The most challenging aspect of working with dates is ensuring that the format of the date being inserted matches the format of the date column in the database.

As long as your data contains only the date portion, your queries will function as expected. However, if a time portion is included, it becomes more complex.

MySQL Date Data Types

MySQL provides the following data types for storing date and date/time values in the database:

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

Note: The date data type for a column is specified when you create a new table in your database!

Working with Dates

Consider the following table:

Orders Table

OrderID	ProductName	OrderDate
1	Geitost	2008-11-11
2	Camembert Pierrot	2008-11-09
3	Mozzarella di Giovanni	2008-11-11
4	Mascarpone Fabioli	2008-10-29

To select the records with an OrderDate of “2008-11-11” from the table above, use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate=‘2008-11-11’

The result set will look like this:

OrderID	ProductName	OrderDate
1	Geitost	2008-11-11
3	Mozzarella di Giovanni	2008-11-11

Note: Comparing two dates is straightforward if there is no time component involved!

OrderID	ProductName	OrderDate
1	Geitost	2008-11-1113:23:44
2	Camembert Pierrot	2008-11-09 15:45:21
3	Mozzarella di Giovanni	2008-11-11 11:12:01
4	Mascarpone Fabioli	2008-10-2914:56:59

Using the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate=‘2008-11-11’

We won’t get any results! This is because the query is specifically searching for dates without a time portion.

Tip: For simplicity and easier maintenance of queries, avoid using time components in your dates unless necessary!