intro
Discover how to compare date and datetime values using SQL operators across MySQL, PostgreSQL, SQL Server, and Oracle.
Compare date, datetime, and time values in an SQL query is a common requirement.
For example, you may want to find all records after a certain date or determine if one timestamp is greater than another. Thanks to the many SQL datetime comparison operator available, that’s an easy task!
Compare Datetimes in SQL With Standard Comparison Operators
In SQL, you can compare date or datetime values using the standard comparison operators (=, <, >, <=, >=) just like numbers. That is possible as long as the values are of a valid date, datetime, or time data types.
That is possible because all major SQL databases support direct datetime comparison. For example, to get rows where an event’s timestamp is after Jan 1, 2025:
1
SELECT *
2
FROM events
3
WHERE event_timestamp >= '2025-01-01';
In the above query, we compare a datetime column (event_timestamp) to a string literal '2025-01-01'. SQL will interpret the string as a date (in ANSI or the default format) and perform the comparison.
In practice, '2025-01-01' is treated as 2025-01-01 00:00:00 (midnight at the start of the day). Thus, any event_timestamp on Jan 1, 2025 at exactly midnight or later will match. If you want strictly after that date (excluding the whole day of Jan 1), you could use > and '2025-01-01 23:59:59' or better, '2025-01-02' as the cutoff.
Notes:
All standard comparison operators work for datetime values. One thing to note is how different database systems treat string literals as dates:
How Date, Datetime, and Time Comparisons Work
When comparing dates or timestamps, SQL uses chronological ordering. In particular, an earlier date/time is considered “less than” a later date/time.
For example, '2025-01-01' < '2025-01-02' will yield true, because January 1, 2025 is before January 2, 2025. This works consistently in all SQL databases. You can compare a date/datetime column to a literal as shown, or compare two date columns to each other:
1
-- Find orders where payment date is after order date (i.e., paid late)
2
SELECT *
3
FROM orders
4
WHERE payment_date > order_date;
This will return rows where payment_date is chronologically later than order_date. Similarly, you can check equality or inequality of dates/times:
1
-- Find events that started and ended on the same date
2
SELECT *
3
FROM events
4
WHERE DATE(start_time) = DATE(end_time);
(Assuming start_time and end_time are datetimes, here we cast or extract the date portion to compare just the date.)
Time Component Consideration
If your datetime values include a time portion, be careful when comparing to a date literal. As mentioned, '2025-01-01' is essentially midnight of that day.
Any datetime on the same day but later than 00:00:00 will be considered greater than '2025-01-01'. For example, '2025-01-01 10:45:00' > '2025-01-01' is true, because the first includes a time 10:45am which is later on the same day. Conversely, if you do event_timestamp = '2025-01-01', this will only match rows exactly at midnight on Jan 1, 2025.
Thus, it’s often better to use a range for date-only comparisons:
1
SELECT * FROM events
2
WHERE event_timestamp >= '2025-01-01'
3
AND event_timestamp < '2025-01-02';
This way, you capture the entire day of Jan 1st. In this scenario, consider also using the SQL BETWEEN operator.
If you need to ignore the time part altogether, you can cast the datetime to a date (which truncates the time). For example, in Postgres ::date cast or the DATE() function (MySQL) can extract the date. Then you can compare dates to dates:
1
-- Find events that occurred on the same calendar date
2
SELECT * FROM events
3
WHERE DATE(start_time) = DATE(end_time);
Keep in mind using functions on columns can prevent indexes from being used, so for large data sets a range comparison as shown earlier is preferred for performance.
Tips and Edge Cases
Conclusion
In summary, comparing datetimes in SQL is straightforward: use < or > for before/after, = for equality (same moment in time), and consider the date vs datetime nuances. All SQL databases allow direct date comparisons, so you can confidently use these operators. Just ensure your values are in date/datetime format and be mindful of the time portion when doing equality or range checks.
(If you need to compute the difference between two datetime values, look into functions like DATEDIFF (SQL Server, MySQL) or date arithmetic functions (PostgreSQL, Oracle)—since the question is about comparing, we focused on <, > and similar comparisons.)
