SQL Query to Compare Results With Today’s Date
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
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
GETDATE() function: This function is used to return the present date and time of the database system.
- 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‘
To check the current date
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.
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.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have the following demo_table in our geek’s database.
CREATE TABLE demo_table( NAME VARCHAR(20), ITEM varchar(20), date DATE);
Step 4: Insert data into a table
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
SELECT * from demo_table;
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.
SELECT column_name, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END FROM table_name;
SELECT NAME, ITEM,date, CASE WHEN date=GETDATE() THEN 'Today' WHEN date<GETDATE() THEN 'Lesser' ELSE 'Greater' END AS "AFTER COMPARISON" FROM demo_table;