How to Compare Date in SQL? Query with Examples

In this SQL tutorial, we will learn – How to Compare Date in SQL? We will understand it with handling multiple cases such as – How to compare DATE in SQL with current date OR with TimeStamp / Without Time and all other cases. So, let’s get started, Step by Step –

---

Setting up the DB to Compare DATE in SQL

To understand the comparison between dates, first, we must have a Database and a table with some records having DATE fields. Here’s the SQL code with MySQL on Windows 10 machine for creating a database named ‘DB_SKL‘, along with a table TBL_MOVIES with some records –

 

1) Creating DB and Tables

CREATE DATABASE DB_SKL;

USE DB_SKL;

CREATE TABLE TBL_MOVIES (

    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    movie_name VARCHAR (255) NOT NULL,
    price NUMERIC (5, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL

); ALTER TABLE TBL_MOVIES AUTO_INCREMENT = 101;

2) Inserting some Records

INSERT INTO tbl_movies (
    movie_name,
    price,
    start_date,
    end_date
)
VALUES
    (   -- First record
        'Steve Jobs: 14 Years in Apple',
        25.50,
        '20200422',
        '20201126'
    ),
    (   -- Second record
        'ShubhamKLogic.com: A Tech Biography',
        30.00,
        '20210101',
        '20310101'
    ),
    (   -- And a few more...
        'Dangal',
        28.50,
        '20180201',
        '20181201'
    ),
    (
        'Avengers: End of Game',
        28.00,
        '20190123',
        '20190422'
    ),
    (
        'Friends',
        28.50,
        '20190123',
        '20190422'
    ),
    (
        'The Family Man',
        28.00,
        '20190123',
        '20190422'
    ),

    (
        'Life of Pie',
        28.50,
        '20160901',
        '20170101'
);

 

3) Structure and Inserted Records (Till now) –

---

Cases & Examples : Compare DATE in SQL

So far, we have defined a Database schema DB_SKL, Created a Table TBL_MOVIES and Inserted multiple records in it, as you can see in the above screenshot of the database table.

If you didn’t understand any of the above SQL Queries, chances are, your SQL Core Concepts are not strong. There are some really good explanations in Official documentation or You can also try MySQL / Oracle / Microsoft SQL Server OR PostgreSQL databases related Top performing course, helping millions of students on Udemy. And, You will definitely learn a lot from them.

Now, Let’s have a look at different cases and Real-life scenarios to implement SQL compare DATE –

 

(1) Compare DATE in SQL : “without time”

Let’s say, we need the list of movies that are released on the first day of the year 2021. Here, we only need to compare the date ‘01-01-2021‘ without time. Here is a possible SQL query to Compare date –

• SQL :
  Here is a possible solution to compare date in SQL without time –

SELECT *
FROM   TBL_MOVIES
WHERE  Date(START_DATE) = '2021-01-01';

 

• Result :
  Once you run the above SQL Date Query, you will successfully get the following result –

 

Notes*** : In above SQL, we used the DATE( ) function, which extracts the date part from a DateTime expression. It’s one of the super useful functions like- LTRIM(), SUBSTRING_INDEX(), and similar that you can use in different scenarios. If you want to learn about SQL Functions, Joins, Trigger and all other concepts Step-by-Step, here’s the SQL – Beginner to Expert course (having 320+ lectures & 150,000+ Students) on Udemy, which you should check. It will really help you to be an expert in SQL.

---

(2) Compare DATE in SQL : “with time”

Let’s say, we need the list of Movies, which are released before Year 2021 and in Midnight ( i.e. 12:00 a.m.).

• SQL :
  Here is a possible solution to compare date in SQL with time –
  

SELECT *
FROM   TBL_MOVIES
WHERE  Year(START_DATE) < 2021
       AND Time(START_DATE) = '12:00:00';

 

• Result :
  Once you run the above SQL Date Query, you will successfully get the following result –

 

 

Notes*** In above Query, We used the YEAR( ) and TIME( ) function of MySQL. If you are using the Oracle Database, you can use Time format ‘HH24:MI:SS‘ or the EXTRACT( ) function. To learn more about Oracle database concepts in-depth, do check its Official documentation or Learn with Oracle : 11g/12c – Become Junior DBA, a highly popular course on Udemy (helped 20k+ students till the date). You will definitely learn a lot from there.

---

(3) Compare DATE in SQL : using “where clause”

Let’s say, we need the list of movies that are NOT released in Mid-night, then here we need to use the NOT operator ‘!‘ with where clause, Like this –

• SQL :
  Here is a possible solution to compare date in SQL using WHERE clause –
  

SELECT *
FROM   TBL_MOVIES
WHERE  Time(START_DATE) != '12:00:00';

 

• Result :
  Once you run the above SQL Date Query, you will successfully get the following result –

 

 

Notes*** In the above MySQL compare date query, we used the TIME() function with != operator to not to select the movies having START_DATE Time = 12:00:00. If you’re using Microsoft SQL Server, the TIME() function will NOT work there. You can use cast() function as- SELECT cast(AttributeDate as Time) FROM theTable. To learn more about MSSQL database Functions, Triggers and other important concepts in-depth, visit its Official documentation or Check Microsoft : SQL for Beginners, a highly popular course on Udemy (helped 40,000+ students till the date). And, You’ll definitely learn almost everything from it.

---

(4) Compare DATE in SQL : with “TimeStamp”

Let’s say, we need the list of movies whose End_Date timestamp is 2019-12-31 03:00:00 . So, Here is a possible Query to find those movies –

• SQL :

SELECT *
FROM   TBL_MOVIES
WHERE  END_DATE = '2019-12-31 03:00:00';

 

• Result :

Once you run the above SQL Date Query, you will successfully get the following result –

 

Notes*** The above SQL Compare Date Query works perfectly fine in almost all the major databases as Oracle, Microsoft SQL Server, PostgreSQL Databases without any change.

---

Example 5) SQL: Compare Dates greater than “- – -“

Let’s say, we need the list of movies, which are released after the date 2021-01-01. Here’s a possible solution to fetch that –

• SQL :

SELECT *
FROM   TBL_MOVIES
WHERE  Date(START_DATE) > '2021-01-01';

 

• Result :

Once you run the above SQL Date Query, you will successfully get the following result –

---

Wrap Up 

So far, with your support, we have learned – How to compare Date in SQL? We also understood comparing SQL DATEs within different cases such as – With Time, Without Time, WHERE clause, with Timestamp, and 5 examples.

If you have any doubt/queries, please do comments or send me a personal email via Google, and I’ll try to provide you a solution within a day.

And, don’t forget to share it with your best social media friends to show your knowledge. Also, check the below resources to be a better Programmer with SQL and Databases –

Better Resources (100%) :

 

SQL Topics (Read Next)