SQL 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.
SQL Date Data Types
The following data types are included with MySQL so that you can store dates or dates and times 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
The following data types are included with SQL Server to store dates or date/time values in databases:
- DATE – format YYYY-MM-DD
- DATETIME – format: YYYY-MM-DD HH:MI:SS
- SMALLDATETIME – format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP – format: a unique number
Remark: When you make a new table in your database, the date types are selected for a field!
SQL 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:
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 apply the previously mentioned SELECT statement:
We won’t receive any feedback! This is a result of the query solely searching for dates devoid of a time component.
Advice: Unless absolutely necessary, avoid using time components in your dates to make your queries concise and manageable!
