MySQL Dates
MySQL Dates
Making sure the format of the date you are trying to insert matches the format of the date column in the database is the most challenging aspect of working with dates.
Your queries should function as expected as long as your data just includes the date part. It becomes more difficult, though, if there is a temporal component involved.
MySQL Date Data Types
MySQL comes with 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
- YEAR – format YYYY or YY
Note: The date data type are set for a column when you create a new table in your database!
Working with Dates
Look at 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 |
We now want to pick the records from the above table that have an OrderDate of “2008-11-11”.
The SELECT statement we employ is as follows:
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: If there is no time component involved, two dates can be compared with ease!
Assume for the moment that the “Orders” table has the following appearance (note the additional time component in the “OrderDate” column):
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 13: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-29 14:56:59 |
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time portion.
Advice: Unless absolutely necessary, avoid using time components in your dates to make your queries concise and manageable!
