Skip to main content
HomeTutorialsSQL

Mastering SQL Date Comparisons: How to Query for Dates Greater Than a Specific Date

Learn how to query and filter a date greater than a specific date in SQL. Compare how the date greater than logic differs in MySQL, PostgreSQL, and SQL Server.
Jun 2024  · 11 min read

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. You can also take the Creating PostgreSQL Databases course to master the ins and outs of PostgreSQL specifically.

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.

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. 


Photo of Allan Ouko
Author
Allan Ouko
I create articles that simplify data science and analytics, making them easy to understand and accessible.

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.

Topics

Learn SQL with DataCamp

Course

Introduction to SQL

2 hr
730K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

cheat sheet

SQL Basics Cheat Sheet

With this SQL cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

5 min

tutorial

SQL: Reporting and Analysis

Master SQL for Data Reporting & daily data analysis by learning how to select, filter & sort data, customize output, & how you can report aggregated data from a database!
Hafsa Jabeen's photo

Hafsa Jabeen

37 min

tutorial

How to Best Use the SQL LIMIT Clause

Learn the application of the SQL LIMIT clause to filter data. Master the use of the LIMIT clause in PostgreSQL and MySQL databases.
Allan Ouko's photo

Allan Ouko

8 min

tutorial

SQL Tutorial: How To Write Better Queries

Learn about anti-patterns, execution plans, time complexity, query tuning, and optimization in SQL.
Karlijn Willems's photo

Karlijn Willems

35 min

tutorial

Hacking Date Functions in SQLite

In this tutorial, learn how to use date functions in SQLite.
Hillary Green-Lerman's photo

Hillary Green-Lerman

3 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

See MoreSee More