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.

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

GET 25% OFF

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

UP TO 70% OFF

Pros

• A wide range of learning programs
• University-level courses
• Easy to navigate
• Verified certificates
• Free learning track available

Main Features

• University-level courses
• Suitable for enterprises
• Verified certificates of completion

FREE COURSES

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.