How to Use the Correct SQL Date Format

SQL Working With Dates: Main Tips

• The date format you need to insert into database must match the date column format.
• A time portion in your data makes queries get more complicated.
• Select data type for a column when creating a table in your database.
• Keep your queries easy to maintain and simple by not allowing time components in your dates.

SQL Date: Data Types

MySQL data types for date/time or a date:

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

SQLServer data types for a date/time or a date:

• DATETIME - YYYY-MM-DD HH:MI:SS
• DATE - YYYY-MM-DD
• TIMESTAMP - an exclusive number
• SMALLDATETIME - YYYY-MM-DD HH:MI:SS

SQL Working with Dates

Two dates can be compared easily when there is no time component.

Here is a "Birthdays" table:

Select people with a Birthday of "1991-01-11" from this table with a SELECT statement:

SELECT * FROM Birthdays WHERE OrderDate='1991-01-11'

This is the result:

Let's add a timestamp to a Birthday:

We use the same SELECT statement:

SELECT * FROM Birthdays WHERE OrderDate='1991-01-11'

Nothing will happen this this code, because there is a time portion and the query is searching for dates with no time portion.