Open In App

SQL Query to Compare Results With Today’s Date

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In this article, we will see SQL Query to Compare results with Today’s date by comparing the data with today’s date using GETDATE() function of SQL.

For comparison of the dates, we can use the CASE() function

GETDATE() function: This function is used to return the present date and time of the database system.

Features:

  • It returns the current date and time of the system.
  • It comes under Date Functions.
  • It does not take any parameters.
  • It returns output in the format – ‘YYYY-MM-DD hh:mm: ss. mmm‘

QUERY:

To check the current date

SELECT GETDATE();

Output:

CASE(): This statement contains one or more conditions with their corresponding result. It is similar to the IF_ELSE statement in which once a condition is met, it stops reading and returns the corresponding result.

If no condition is met, it returns the value present in the ELSE statement. If the ELSE statement is absent, a NULL value is returned.

Syntax:

 CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Now, take an example to compare results with todays date in MS SQL Server.

Follow the given below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database.

Query:

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

Step 4: Insert data into a table

Query:

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');

Step 5: See the content of the table

Query:

SELECT * from demo_table;

Output:

Step 6:  Compare the result with today’s date

For this, we will return a column named ‘After comparison’ which returns a value after comparing today’s date with the value in the ‘Deliver’ column. After comparison column contains the following string:

  • Lesser than- If the date is less than today’s date
  • Greater- If the date is greater than today’s date
  • Today- If the date is the same as today’s date.

Syntax:

SELECT column_name,
CASE  
       WHEN condition1 THEN result1
       WHEN condition2 THEN  result2
       ELSE result3  
END 
FROM table_name;

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:


Last Updated : 19 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads