SQL Compare Dates

This article will explore how to compare dates while working with SQL databases. Keep in mind that the comparison may vary depending on the database engines. Hence, we will try to stick to the universal methods that are applicable to all engines.

SQL Compare Dates – Operators

The main method of comparing dates in SQL is to use comparison operators. SQL supports comparison operators such as:

Equal to (=)
Less than (<)
Greater than (>)

Using the comparison operators and conditional expressions such as an if statement, we can perform a comparison on date types in SQL.

The first step is to declare a variable that can hold the date value. For this, we can use the DECLARE statement.

Once declared, we can assign a value to the variable using the SET keyword followed by the date value.

Consider the example shown below:

DECLARE @date1 DATE;
DECLARE @date2 DATE;
SET @date1='2022-04-01';
SET @date2='2021-04-01';
IF @date1=date2
SELECT 'dates are equal'
ELSE SELECT 'dates not equal';

In the above SQL script, we declare two dates variables. We then assign two different dates to each variable.

Next, we use an if statement and a comparison operator to check if the dates are equal.

We can implement other conditions using if-else and other comparison operators. An example is shown below:

DECLARE @date1 DATE;
DECLARE @date2 DATE;
SET @date1='2022-04-01';
SET @date2='2021-04-01';

IF @date1=date2
SELECT 'dates are equal'
ELSE
IF @date1<date2 SELECT 'date2 is greater than date1';

SQL Select Where

We can also use comparison operators to get columns where the date matches a specific condition. We can express the syntax as shown:

SELECT COLUMN(s) FROM TABLE_NAME WHERE column_value < 'date_expression'

For example, we can fetch for only the rows where the date is less than ‘2022-04-01’

SELECT * FROM my_table WHERE purchase_date < '2022-04-01';

SQL Between

We can also use the BETWEEN statement to get the records matching a specific date range. An example usage is as shown:

SELECT * FROM my_table WHERE purchase_date BETWEEN '2022-04-01' AND '2021-04-01';

The example should return the records where the purchase_date matches the specified range.

Conclusion

This article explores how to perform date comparison in SQL using comparison operators. How to select records matching specific date ranges using the WHERE and BETWEEN operators was also covered in this article.

SQL Compare Dates