SQL Dates

The most challenging aspect of working with dates is ensuring that the date format you’re trying to insert matches the format of the date column in the database.

Queries operate as expected when data solely contains the date portion. Yet, when a time component is included, complexity arises.

SQL Date Data Types

MySQL offers various data types specifically designed for storing date or date/time values 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

SQL Server 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
SMALLDATETIME: Format: YYY-MM-DD HH:MI:SS
TIMESTAMP: Format: A unique number

Remember: When creating a new table in your database, you select the appropriate date types for each column.

SQL Working with Dates

Take a look at the table below:

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 retrieve records with an OrderDate of “2008-11-11” from the table above, we employ the subsequent SELECT statement:

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

The resulting set will appear as follows:

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

Note: Comparing two dates is straightforward when there’s no time component included.

Now, consider the modified “Orders” table depicted below, where a time component has been added to 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 utilize the identical SELECT statement as previously:

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

No results will be returned. This occurs because the query exclusively seeks dates without a time component.

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