SQL Query to Check if Date is Greater Than Today in SQL

Managing dates effectively is critical in SQL for performing comparisons and scheduling. One common scenario is to check if a date in a database is greater than today’s date. In SQL, this can be achieved using the GETDATE() function, which retrieves the current system date and time in the format YYYY-MM-DD hh:mm:ss.mmm. By using the GETDATE() function and comparison operators effectively, we can ensure our SQL queries are accurate and up-to-date, making it easier to manage dates, schedules, and deadlines in various applications.

What is the GETDATE() Function in SQL ?

GETDATE() is a built-in function in SQL used to retrieve the current system date and time. It is commonly used in queries for comparing dates and performing time-sensitive operations. To check if a specific date is in the future, we can use this function in combination with comparison operators, such as >. By using this function, you can easily filter out past dates and focus on upcoming events.

Features of GETDATE()

• Returns the current system date and time.
• This function comes under Date Functions.
• This function doesn’t accept any parameter.
• This function returns output in ‘YYYY-MM-DD hh:mm: ss. mmm‘ format.

Retrieving the Current Date

The GETDATE() function in SQL retrieves the current system date and time in the format YYYY-MM-DD hh:mm:ss.mmm. It is often used to check if a specific date in our database is greater than today’s date.

Query:

SELECT GETDATE();        

Output

2024-12-15 14:30:00.000

Checking Dates Greater Than Today’s Date

To demonstrate how to check if a date is greater than today’s date, let’s create a sample table called geeksforgeeks. This table will store order and delivery dates. Considering a scenario where we need to retrieve all the events scheduled after today’s date from our database.

Query:

CREATE TABLE geeksforgeeks(
NAME VARCHAR(20),
Ordered DATE,
Deliver DATE);

INSERT  INTO geeksforgeeks VALUES
 ('ROMY', '2021-01-16', '2021-03-12'),
('AVINAV', '2021-11-12', '2021-12-12'),
 ('PUSHKAR', '2021-06-23', '2021-10-13');

SELECT * FROM geeksforgeeks;

Output

geeksforgeeks

Example 1: Check for Deliver Dates Greater Than Today

To identify rows where the delivery date is greater than today’s date, We can use the GETDATE() function combined with the > operator in a SQL query:

SELECT * FROM geeksforgeeks WHERE Deliver > GETDATE();

Output

Explanation:

Assuming today’s date is 2024-12-15, the query returns AVINAV and PUSHKAR having delivery date (2024-12-25, 2024-12-16) later than today.

Example 2: Check for Ordered Dates Greater Than Today

Similarly, we can check which orders were placed after today’s date using the following query:

SELECT * FROM geeksforgeeks WHERE Ordered > GETDATE();

Output

Example 3: Combine Conditions for Advanced Filtering

We can combine conditions to filter rows based on both the order and delivery dates. For example:

SELECT * FROM geeksforgeeks 
WHERE Ordered > GETDATE() AND Deliver > GETDATE();

Output

Explanation

Only the row for AVINAV meets both conditions i.e Ordered is after today’s date (2024-12-15) and Deliver is also after today’s date.

Conclusion

The GETDATE() function is a powerful tool for handling current date and time operations in SQL. By using it in date comparisons, we can efficiently filter rows based on dynamic, time-sensitive conditions. This function is particularly useful in applications like tracking deadlines, monitoring future schedules, and creating real-time reports. Using GETDATE() allows developers to handle time-sensitive logic without hardcoding dates, making queries more flexible and maintainable. Additionally, combining it with other SQL functions and operators can further enhance its utility, enabling complex date manipulations and comparisons.

FAQs

What is the difference between GETDATE() and CURRENT_TIMESTAMP?

Both return the current system date and time, but GETDATE() is specific to SQL Server, while CURRENT_TIMESTAMP is ANSI-compliant and works across multiple DBMS.

How do I compare dates in a different format?

For non-standard formats, we may need to use the CONVERT() function.
Example: SELECT * FROM geeksforgeeks WHERE CONVERT(DATE, Deliver, 104) > GETDATE();

How do I ignore the time part when comparing dates?

Use the CAST() or CONVERT() functions to remove the time part:
SELECT * FROM geeksforgeeks WHERE CAST(GETDATE() AS DATE) = Ordered;

romy421kumari

Follow

Improve

Article Tags :

• Databases
• SQL
• SQL-Query
• SQL-Server