SQL Query to Compare Results With Today's Date

In SQL, comparing results with today's date is a powerful tool for filtering data, managing schedules, including managing tasks, appointments and performing time-sensitive analysis. By using SQL's GETDATE() function we can easily perform this comparison. The ability to filter records based on date criteria allows for effective data analysis, report generation, and database management.

In this article, we will explain the process of setting up a database, creating a table, inserting sample data, and using SQL functions to compare date values with today's date. We will cover each step in detail to ensure a thorough understanding, complete with SQL commands, sample outputs, and explanations.

Setting Up the Database

Before we can perform date comparisons, we need to set up a database and a table to store our data. This preparation is essential for demonstrating how date comparisons work in SQL.

Step 1: Create a New Database

First, we create a new database where our table will reside. After creating the database, then we select it as the current database. This is done using the following SQL command.

CREATE DATABASE geeks;
USE geeks;

Step 2: Create a Demo Table

Next, we create a table named demo_table with columns to store relevant data. This table will hold information about names, items, and dates:

CREATE TABLE demo_table (
    NAME VARCHAR(20),
    ITEM VARCHAR(20),
    date DATE
);

Step 3: Insert Sample Data

Now, we need to populate our demo_table with some sample data to demonstrate how date comparisons work:

INSERT INTO demo_table 
VALUES 
('Romy', 'shirt', '2021-10-21'),
('Shalini', 'shoes', '2021-10-14'),
('Sambhavi', 'hat', '2021-10-10'),
('Pushkar', 'mobile', '2021-11-21'),
('Nikhil', 'home_decor', '2021-09-09');

To verify the data, use:

SELECT * FROM demo_table;

Output

Query to Select Today's Date

To compare our date values with today's date, we first need to know what today's date is. Both functions return the current date in their respective formats. This date is crucial for filtering and comparing data stored in our database. Depending on the SQL database system, the function to get today's date varies.

• For SQL Server: Use the GETDATE() function:

SELECT GETDATE();

• For MySQL: Use the CURRENT_DATE() function:

SELECT CURRENT_DATE();

Compare the result with today's date in SQL

To compare dates with today’s date in SQL, the process involves selecting the relevant columns from a table (in this case, demo_table) and utilizing the CASE() function to categorize each record based on its date. In the CASE() function, three conditions are used:

1. WHEN date = GETDATE() THEN 'Today': This condition checks if the date in the demo_table matches today’s date. If it does, the record is marked as 'Today'.
2. WHEN date < GETDATE() THEN 'Lesser': This condition evaluates if the date in the demo_table is earlier than today’s date. If it is, the record is marked as 'Lesser'.
3. ELSE 'Greater': If neither of the above conditions is met (meaning the date is later than today’s date), then the record is classified as 'Greater'.

The SQL query for comparing dates involves selecting the columns we want from our demo_table and using the CASE() function to perform the comparison:

Query:

SELECT NAME, ITEM,date,
CASE  
    WHEN date=GETDATE() THEN 'Today'
    WHEN date<GETDATE() THEN 'Lesser'
    ELSE 'Greater'  
END AS "AFTER COMPARISON"
FROM demo_table;

Output

Explanation:

The output from the SQL query provides a clear categorization of each record in the demo_table based on its date in relation to today’s date.

• The 'After Comparison' column uses the CASE() function to label each record as 'Lesser', 'Greater', or 'Today' depending on the comparison between the date in the table and the current date.
  
• Records with dates before today are marked as 'Lesser', indicating they occurred earlier in time. Dates after today are labeled as 'Greater', showing they are in the future relative to today.
  
• Records where the date matches today’s date are marked as 'Today', providing a snapshot of current or ongoing activities. This output allows users to quickly understand the timing of events relative to the present date

Conclusion

By using SQL’s GETDATE() or CURRENT_DATE() functions in combination with the CASE() function, we can easily compare dates in our database with today's date. This approach allows for effective filtering and classification of data, making it an invaluable tool for managing time-sensitive data in SQL. Whether we are using SQL Server, MySQL, or another SQL-compatible database, mastering date comparisons will enhance our ability to work with temporal data efficiently.