SQL Query to Compare Two Dates

In SQL, dates are complicated for newbies, since while working with the database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, DateTime (time is also involved with date) is used. Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.

Syntax:

 IF Boolean_expression    

   { sql_statement | statement_block }  

[ ELSE    

   { sql_statement | statement_block } ]  

We can declare variables easily by using the keyword DECLARE before the variable name. By default, the local variable starts with @.

Syntax:

DECLARE @variable_name datatype;

Set values to the variable: We can assign values to the variables using the SET keyword.

Syntax:

SET @variable_name;

Now we take different cases to demonstrate of comparison between dates.

Query 1:

DECLARE @date1 DATE, @date2 DATE;                               
SET @date1='2021-01-01';
SET @date2='2021-02-02';                                       
IF @date1=@date2                                                
SELECT 'equal date'
ELSE
IF @date1<@date2 SELECT 'date2 is greater'              
ELSE SELECT 'date1 is greater';                         

Output:

Query 2: 

DECLARE @date1 DATE, @date2 VARCHAR(20);
SET @date1='2021-01-01';
SET @date2='2021-01-01';
IF @date1=@date2
SELECT 'equal date'
ELSE
IF @date1<@date2 SELECT 'date2 is greater'
ELSE SELECT 'date1 is greater';

Output:

Query 3:

DECLARE @date1 DATE, @date2 VARCHAR(20);
SET @date1='2022-01-01';
SET @date2='2021-01-01';
IF @date1=@date2
SELECT 'equal date'
ELSE
IF @date1<@date2 SELECT 'date2 is greater'
ELSE SELECT 'date1 is greater';

Output: