How to Compare Date Values in SQL

Problem:

You want to compare two date values in SQL.

Example 1:

The database for an online store has a table named orders with data in the columns order_id and order_date.

We want to find information for each order placed after 2021, i.e., since the start of 2022.

Solution 1:

Filter down to the users who placed an order after 2021 by order_time in the WHERE clause. When comparing dates, use regular comparison operators: <, >, =, <=, and/or >=.

In this example, you want to compare order_date with the date '2022-01-01':

SELECT *
FROM orders
WHERE order_date >= '2022-01-01';

Alternatively, use the condition > '2021-12-31':

SELECT *
FROM orders
WHERE order_date > '2021-12-31';

Either way, here’s what you get:

Example 2:

Let’s now compare two columns with date values.

The database for an online school has a table named assignments with data in the columns assignment_id, due_time, and hand_in_time.

Let’s find information on the assignments handed in on time, i.e., where hand_in_date is earlier than or equal to due_time.

Solution 2:

In this example, compare due_date with hand_in_date. Make sure due_date is greater than or equal to hand_in_date:

SELECT *
FROM assignments
WHERE hand_in_date <= due_date;

Here’s the result:

Discussion:

To compare dates in SQL, use the usual comparison operators <, <=, >, >=, and/or =. When comparing dates, the earlier date is the “lesser” date, and the later date is the “greater” date. For example, 2021-09-15 is greater than 2021-09-14, and 2021-01-10 is less than 2022-01-10.

When using comparison operators, you may use two date columns (like in the second example), or you may compare a column to a date in the string format. When comparing a date column to a constant date, the constant must be in quotes. The default format in most databases is YYYY-MM-DD, that is, the year first, then the month, then the day (e.g., ‘2021-07-22’).