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 an aspiring data practitioner, I recommend taking DataCamp's Introduction to SQL course to familiarize yourself with querying data from databases. You should also take the Learn SQL course to master different analysis methods in relational databases like MySQL, SQL Server, and PostgreSQL.

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.

-- 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.

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.