course
Mastering SQL Date Comparisons: How to Query for Dates Greater Than a Specific Date
Understanding how to query for dates greater than a specific date is crucial for effective database management and analysis. This tutorial will provide a comprehensive guide to performing date comparisons in SQL across different SQL database systems, including SQL Server, PostgreSQL, and Oracle. Whether you're dealing with historical data analysis, reporting, or filtering records, mastering this technique will enhance your SQL querying capabilities.
By the end of this guide, you will be able to:
- Understand the nuances of date formats and functions in different SQL databases, including SQL Server, PostgreSQL, and Oracle.
- Apply practical examples to real-world scenarios.
If you are a data analyst or data scientist ready to learn more, I recommend taking DataCamp's SQL Fundamentals skill track to familiarize yourself with querying data from databases. And for those eager to expand their database expertise, check out the Database Design course, which provides insights into building efficient databases.
Associate Data Engineer in SQL
Introduction to SQL Date Comparisons
Date comparison is important in SQL as it allows data professionals to filter and sort data during analysis. The method is especially useful in financial reporting or inventory management, which requires trend analysis.
SQL’s ‘greater than’ operator (>
) is used with dates to compare two date values. When you include the ‘greater than’ operator in an SQL query with dates, the query will return records with dates later than the specified date. For example, the query below will return rows in the Orders
table where the OrderDate
is later than 2023-01-01
.
SELECT *
FROM Orders
WHERE OrderDate > '2023-01-01';
Let's start by quickly reviewing the basic SQL operators that can be used to filter data. These operators are also covered in our SQL Basics Cheat Sheet, which you can review before you try the examples below.
Equal to (=)
The =
operator is used in SQL to compare whether two values are equal. Below is an example of when the operator is used with a date value. The SQL query will return rows in the Orders
table where the OrderDate
equals 2023-01-01
.
-- Select all orders placed on January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate = '2023-01-01';
Not equal to (!= or <>)
The !=
or <>
operator checks two values and returns those that are not the same as those referenced. For example, when used with a date value, the !=
or <>
operator will return rows that are not the same as the referenced date. You can also read our SQL NOT EQUAL Operator: A Beginner's Guide tutorial to learn the subtleties.
-- Select all orders that were not placed on January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate != '2023-01-01';
-- Alternative syntax using <>
SELECT *
FROM Orders
WHERE OrderDate <> '2023-01-01';
Less than (<)
The <
operator is used in SQL to check whether the value on the left is smaller than on the right. When used in an SQL query with date values, the query will return records with dates earlier than the specified date. For example, the query below will return rows in the Orders
table where the OrderDate
is earlier than 2023-01-01
.
-- Select all orders placed before January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate < '2023-01-01';
Greater than or equal to (>=)
The >=
operator checks whether values on the left of the operator are greater than or equal to the value on the right. When the operator is used in SQL to query a date value, it will return records where the date values are only equal to or greater than the value being referenced.
-- Select all orders placed on or after January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate >= '2023-01-01';
Less than or equal to (<=)
The <=
operator is used in SQL to check whether values on the left side of the operator are less or equal to the value on the right. When the operator is used to query a date value, it will return records where the date values are only equal to or less than the value being referenced.
-- Select all orders placed on or before January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate <= '2023-01-01';
Date Comparison in SQL Server
We can compare dates in SQL Server using the following operators: <
, <=
, >
, >=
, and =
. However, we will cover the following:
Using the ‘greater than’ operator (>)
The ‘greater than’ (>
) operator is used in SQL Server to filter out dates later than the date being compared on the right side of the operator. For example, the query below filters the Orders
table and returns records of orders placed after OrderDate
2023-01-01
.
-- Select all orders delivered after January 1, 2023
SELECT *
FROM Orders
WHERE DeliveryDate > '2023-01-01';
Using the GETDATE() function
The GETDATE()
function in SQL Server returns the current date as recorded in the user’s system. Therefore, you can use this function to query data and filter out rows where the date is greater than the current date.
-- Select all orders delivered after the current date and time
SELECT *
FROM Orders
WHERE DeliveryDate > GETDATE();
Using BETWEEN for date ranges
The BETWEEN
operator in SQL is used to filter values between a given range. When used in SQL Server with dates, you can filter rows containing dates within the stated date. The results will include both the start and end dates as written in the query.
-- Select all orders delivered between January 1, 2023, and December 31, 2023
SELECT *
FROM Orders
WHERE DeliveryDate BETWEEN '2023-01-01' AND '2023-12-31';
Using CAST and CONVERT for date ranges
You can also use the CAST
and CONVERT
functions to prevent errors. These functions ensure that the date values are correctly interpreted and formatted.
-- Convert a string to a date
SELECT CAST('2023-06-26' AS DATE) AS ConvertedDate;
-- Convert a string to a datetime
SELECT CAST('2023-06-26 14:30:00' AS DATETIME) AS ConvertedDateTime;
-- Convert a string to a date using CONVERT
SELECT CONVERT(DATE, '2023-06-26') AS ConvertedDate;
-- Convert a string to a datetime using CONVERT
SELECT CONVERT(DATETIME, '2023-06-26 14:30:00') AS ConvertedDateTime;
Date Comparison in PostgreSQL
PostgreSQL has some different clauses used to compare dates. We discuss some of them below.
Using the ‘greater than’ operator (>)
Similar to the SQL Server database, the >
operator in PostgreSQL filters date greater than the comparison date.
-- Select all orders placed after January 1, 2023
SELECT *
FROM Orders
WHERE OrderDate > '2023-01-01';
Using the EXTRACT function
The EXTRACT
function is used in PostgreSQL to retrieve date fields such as year, month, or day. In the query below, the EXTRACT
clause filters records for the YEAR 2023.
-- Select all orders placed in the year 2023
SELECT *
FROM Orders
WHERE EXTRACT(YEAR FROM OrderDate) = 2023;
Using the INTERVAL function
The INTERVAL
function is used in PostgreSQL to do some date calculations. INTERVAL
allows you to add or subtract specific amounts of time to or from a date.
-- Select all orders placed in the last 30 days
SELECT *
FROM Orders
-- Filter orders placed within the last 30 days from the current date
WHERE OrderDate > NOW() - INTERVAL '30 days';
Using the DATE_PART function
The DATE_PART
function allows one to extract a subfield from the date value. This subfield could be either the date or time. The query below returns orders placed in March, giving a double precision value of 3 for the third month.
-- Select all orders placed in March
SELECT *
FROM Orders
WHERE DATE_PART('month', OrderDate) = 3;
Using the TO_DATE function
The TO_DATE
function in PostgreSQL converts a given string to a date value based on the given format. In the query below, the date value is provided as a string. So you convert as a date to the format YYYY-MM-DD
.
-- Select all orders placed on January 1, 2023, using a string date
SELECT *
FROM Orders
WHERE OrderDate = TO_DATE('2023-01-01', 'YYYY-MM-DD');
Using BETWEEN for date ranges
Like SQL Server, the BETWEEN clause in PostgreSQL filters data within a specific range. For values containing dates, the BETWEEN
function filters and returns rows between the indicated dates. These dates are also included in the results.
-- Select all orders placed between January 1, 2023, and December 31, 2023
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
Date Comparison in Oracle
Using the ‘greater than’ operator (>)
The >
operator filters date that is later than the comparison date.
-- Select all active users with an end date in the future
SELECT *
FROM Users
-- Filter users with status 'Active'
WHERE Status = 'Active'
-- Ensure the end date is in the future
AND EndDate > SYSDATE;
Handling NULL dates and complex conditions
When comparing dates in an Oracle database, it is important to specify complex scenarios, such as empty dates. In this case, we use the IS NULL
clause to filter records with no dates.
-- Select all active users with either a future end date or no end date (NULL)
SELECT *
FROM Users
-- Filter users with status 'Active'
WHERE Status = 'Active'
-- Ensure the end date is either in the future or is NULL (no end date)
AND (EndDate > SYSDATE OR EndDate IS NULL);
Using BETWEEN for date ranges
The BETWEEN
clause is also used in Oracle to filter data within a specific range. For values containing dates, the BETWEEN
function filters and returns records between the indicated dates. These dates are also included in the results.
-- Select all active users with an end date between the current date and a specified future date (e.g., end of the year)
SELECT *
FROM Users
-- Filter users with status 'Active'
WHERE Status = 'Active'
-- Ensure the end date is between the current date and a specified future date (e.g., December 31, 2023)
AND EndDate BETWEEN SYSDATE AND DATE '2023-12-31';
Common Pitfalls and Best Practices
I have encountered issues with dates and the ‘greater than’ operator in SQL. Here are some common mistakes I have discovered and tips on how to avoid them.
Inconsistent date formats
Using inconsistent date formats in your queries will result in errors. You may not get the desired results if you mix date formats such as YYYY-MM-DD
and MM-DD-YYYY
.
To avoid these issues, always ensure you use standard date format. You may also convert the date to your preferred format or according to local conventions using TO_DATE
in PostgreSQL or Oracle databases. For SQL Server, you may use CAST
and CONVERT
functions.
-- Convert a string to a date
SELECT CAST('2023-06-26' AS DATE) AS ConvertedDate;
-- Convert a string to a datetime
SELECT CAST('2023-06-26 14:30:00' AS DATETIME) AS ConvertedDateTime;
-- Convert a string to a date using CONVERT
SELECT CONVERT(DATE, '2023-06-26') AS ConvertedDate;
-- Convert a string to a datetime using CONVERT
SELECT CONVERT(DATETIME, '2023-06-26 14:30:00') AS ConvertedDateTime;
Time zone handling
You may retrieve inaccurate results when handling data across multiple time zones. Moreover, not accounting for time zone differences during date conversion may cause errors.
Therefore, I recommend storing your dates in UTC (Coordinated Universal Time) and using built-in time zone functions to convert dates.
-- Store dates in UTC and convert to local time zone
SELECT *
FROM Orders
-- Filter orders where the OrderDate in UTC is greater than the current date in the 'America/New_York' time zone
WHERE OrderDate AT TIME ZONE 'UTC' > SYSDATE AT TIME ZONE 'America/New_York';
-- Use time zone functions for accurate comparison
SELECT *
FROM Users
-- Filter users where the EndDate in UTC is greater than the current date in UTC
WHERE EndDate AT TIME ZONE 'UTC' > SYSDATE AT TIME ZONE 'UTC';
Avoiding common mistakes with dates
When working with dates, it is important to avoid errors by retrieving only the values you need in your analysis. For example, you may omit the time component in a date value if it is irrelevant to your analysis. Handle the NULL
values explicitly to prevent errors, especially where date values may contain the error.
Writing readable and maintainable queries
You may find yourself writing complex and nested queries, which makes it hard to read and follow the different clauses. Similarly, failing to include comments in your code makes it hard to explain what it does.
Thus, I recommend you include comments explaining the logic behind the clauses. To make the queries readable, you can also use simple subqueries or Common Table Expressions (CTEs). Check out our Reporting in SQL course to learn more about writing readable and maintainable queries for analysis and reporting.
A Note on Why SQL Methods Differ
The diversity in SQL methods for date comparisons across different database systems like SQL Server, PostgreSQL, and Oracle arises from the unique evolution and design philosophies of each database management system. Each system was developed independently and catered to different user needs and compatibility requirements, which led to varied implementations of SQL standards and additional proprietary features.
For instance:
- SQL Server, developed by Microsoft, incorporates functions and syntax that align closely with other Microsoft products, aiming for integration and ease of use in the Windows ecosystem.
- PostgreSQL is an open-source project with a strong emphasis on extensibility. This flexibility encourages a variety of date functions and operators that can be optimized or extended by its community.
- Oracle typically focuses on high scalability and reliability for enterprise applications, leading to specific optimizations in how dates and other data types are handled, often with a focus on complex, high-volume transaction environments.
Conclusion
I encourage you to practice the different skills to know when to apply SQL functions in real-world scenarios. Consider taking DataCamp’s SQL Fundamentals and Intermediate SQL courses. I also recommend the Associate Data Analyst in SQL course for aspiring data analysts and data scientists to help you master the different analysis techniques. These courses will also help you prepare for the SQL Associate Certification, which is great on a resume.
Earn a Top SQL Certification
Frequently Asked Questions
What is a comparison operator?
The comparison operators compare two values and return results whether the values are true or false from the compared value.
What are the common operators used in SQL date values?
The common operators used in SQL date values include equal to (=
), greater than (>
), less than (<
), greater than or equal to (>=
), less than or equal to (<=
).
When is the BETWEEN operator used in SQL?
The BETWEEN
operator filters records within a certain range.
What operator should I use if my dates have NULL values?
You should include the IS NULL
clause to handle null values and avoid errors in query results.
What should I do if my SQL query returns errors when filtering the date value?
Check to ensure your dates are in the correct format. If not, convert them to a consistent format like YYYY-MM-DD
.
Learn SQL with DataCamp
course
Intermediate SQL
course
Joining Data in SQL
cheat-sheet
SQL Basics Cheat Sheet
tutorial
SQL: Reporting and Analysis
tutorial
How to Best Use the SQL LIMIT Clause

Allan Ouko
8 min
tutorial
SQL Tutorial: How To Write Better Queries
tutorial
Hacking Date Functions in SQLite
code-along
Getting Started in SQL

Kelsey McNeillie