Black friday

Save big!

All courses under $5 - for a limited time!

Code has been added to clipboard!

How to Use the Correct SQL Date Format

Reading time 2 min
Published Aug 10, 2017
Updated Oct 10, 2019

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:

ID Name Birthday
1 Tom Jefferson 1991-01-11
2 Antony Fabrizio 1987-05-28
3 Mike Timoti 1991-01-11
4 Gary Peterson 1997-12-19

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

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

This is the result:

ID Name Birthday
1 Tom Jefferson 1991-01-11
3 Mike Timoti 1991-01-11

Let's add a timestamp to a Birthday:

ID Name Birthday
1 Tom Jefferson 1991-01-11 14:24:44
2 Antony Fabrizio 1987-05-28 19:40:29
3 Mike Timoti 1991-01-11 10:11:01
4 Gary Peterson 1997-12-19 23:54:59

We use the same SELECT statement:

Example
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.