SQL Query to Compare Two Dates

Working with dates in SQL can be challenging, especially for beginners. We need to make sure that the format of the date values are consistent throughout the queries, whether you're dealing with simple DATE types or complex DATETIME values.

This article will walk you through the process of comparing two dates using different approaches, including examples and outputs, ensuring clarity and understanding.

In SQL dates are typically stored in DATE or DATETIME format and to compare two dates, we can use comparison operators such as ?

• =: Checks if two dates are equal.
• <: Checks if one date is earlier than another.
• >: Checks if one date is later than another.

The key is ensuring the date format of your input matches the database format, which is generally YYYY-MM-DD for DATE types.

Declaring & Assigning Variables for Dates

For comparing values like dates, different variables are often used, in SQL, variables can be declared and later set by using DECLARE and SET statements respectively, here's a broad context of the statements -

DECLARE @variable_name DATATYPE;
SET @variable_name = 'value';

Using CASE Statement for Comparison

In place of IF-ELSE for date comparison, a CASE statement can be used. This is a simpler and better solution and integrates bigger queries.

Example

DECLARE @date1 DATE = '2024-05-01', @date2 DATE = '2024-06-01';
SELECT CASE
WHEN @date1 = @date2 THEN 'Dates are equal'
WHEN @date1 < @date2 THEN 'date2 is greater'
ELSE 'date1 is greater'
END AS ComparisonResult;

Output

date2 is greater

Comparing Dates in a WHERE Clause

When performing date comparisons, one would usually do that while doing row filtering of a table, remember this procedure when incorporating date comparisons within a WHERE clause.

Example

Suppose you have a table Orders with the OrderDate column.

SELECT * FROM Orders 
WHERE OrderDate > '2024-01-01';

This query retrieves all orders placed after January 1, 2023.

Using DATEDIFF for Detailed Comparisons

DATEDIFF function explains itself in that it returns a difference between two specified dates expressed in days, months or years and this is particularly helpful for knowing the gap between dates.

Example

DECLARE @date1 DATE = '2024-05-01', @date2 DATE = '2024-06-01';
SELECT DATEDIFF(DAY, @date1, @date2) AS DaysDifference;

Output

31

Handling Dates with DATETIME

When dealing with DATETIME, time components can affect comparisons. Always ensure proper handling of time if it is a part of your data.

Example

DECLARE @datetime1 DATETIME = '2024-05-01 14:30:00', 
   @datetime2 DATETIME = '2024-05-01 10:00:00';

IF @datetime1 > @datetime2
    SELECT 'datetime1 is later';
ELSE
    SELECT 'datetime2 is later';

Output

datetime1 is later