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.
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid certificates of completion
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

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.

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 28/02/2021
3176 People Used
Only 97 Left
Rating
5.0