Back to cookbooks list Articles Cookbook

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.

user_idnicknameregistration_date
5637542878superuser2022-02-23
2356782364helloworld2021-05-09
7325629347toolazytothink2021-12-12

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

Download SQL for Data Analysis Cheat Sheet

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:

user_idnicknameregistration_date
2356782364helloworld2021-05-09
7325629347toolazytothink2021-12-12

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.

user_idorder_idorder_datepayment_date
5637542878HN76VH2022-02-232022-02-23
235678236460BM222021-05-092021-05-10
7325629347671YVB2021-12-122021-12-12

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:

user_idorder_idorder_datepayment_date
5637542878HN76VH2022-02-232022-02-23
7325629347671YVB2021-12-122021-12-12

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.

Recommended courses:

Recommended articles:

See also: