How to Compare Datetime Values in SQL

Problem:

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

Example 1:

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

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_time with the datetime '2022-01-01 00:00:00':

SELECT *
FROM orders
WHERE order_time >= '2022-01-01 00:00:00';

Here’s what you get:

Example 2:

Let’s now compare two datetime columns.

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 late, i.e., where due_time is earlier than hand_in_time.

Solution 2:

For this example, compare due_time with hand_in_time. Make sure due_time is less than hand_in_time:

SELECT *
FROM assignments
WHERE due_time < hand_in_time;

Here’s the result:

Discussion:

Datetime is a data type used in MySQL and SQL Server to represent both date and time information together. To compare datetime values in these databases, use the usual comparison operators <, <=, >, >=, and/or =. When comparing datetime values, the earlier datetime is the “lesser” datetime, and the later datetime is the “greater” datetime. For example, 2022-03-10 21:10:01’ is less than 2022-03-10 22:14:01, and 2022-03-10 09:10:01 is greater than 2022-03-09 21:00:41.

When using comparison operators, you may use two datetime columns (as in the second example), or you may compare a column to a datetime in the string format. When comparing a datetime column to a constant date, the constant must be in quotes. The default format in most databases is YYYY-MM-DD hh:mm:ss; that is, the year first, then the month, then the day, then the hour, minute, and second (e.g., '2021-07-22 23:05:04').