How to Compare 2 Dates in the WHERE Clause in SQL

Problem:

You want to compare values of two dates in an SQL WHERE clause.

Example 1:

A social platform’s database has a table named users with data in the columns user_id, nickname, and registration_date.

We want to find information for each user who was registered before 2022, i.e. until the end of 2021.

Solution 1:

To find users that registered before 2022, you’ll need to filter them out by putting registration_date in the WHERE clause. When comparing dates, use regular comparison operators: <, >, =, <=, >=.

In this example, you’ll want to compare registration_date with the date ‘2022-01-01’:

SELECT * 
FROM users 
WHERE registration_date < '2022-01-01';

Alternatively, you could use the condition <= '2021-12-31':

SELECT * 
FROM users 
WHERE registration_date <= '2021-12-31';

Either way, here’s what you’d get:

Example 2:

The social platform’s database also has a table named order with data in the columns user_id, order_id, order_date, and payment_date.

Let’s find information on orders that were ordered and paid for on the same day (e.g. all orders placed and paid on Jan 1, 2022, all orders placed and paid on Jan 3, 2022, etc.).

Solution 2:

In this example, you’ll compare order_date with the payment_date and make sure that they are equal:

SELECT * 
FROM order
WHERE order_date = payment_date;

Here’s the result:

Discussion:

When comparing dates, the lesser date is the older date and vice versa. Comparing dates with <,<=,>,>=,= operators works in every SQL database. You can compare a date to a string in the format of a date (as done above); you can also compare two date columns with these operators.

You can also compare dates with timestamps. In this case, your date will be equal to a timestamp of this date with time 00:00:00. For example, the date ‘2022-02-23’ is the timestamp ‘2022-02-23 00:00:00’. This means that any other time of this date will actually have a bigger value than the date itself: 2022-02-23 is actually smaller than 2022-02-23 10:45:00.