🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

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.
DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
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
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

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.