Sometimes we need to get new datetime values from existing date records. In this case we need to use DATEADD function. This function requires three parameters.
Syntax:
- DATEADD( datepart, number, date);
The number variable adds specified datepart value to date variable. Number interval(integer) is signed integer. It can be negative.
Examples of DATEADD function:
Query 1: If we need to get 30 minute ago. We must use this following query.
- select getdate(), DATEADD (minute , -30 , GETDATE() ) AS "30 Minutes ago"
Output: The getdate() function will display the current time and date but DATEADD is use to get the desired output.
Query 2: In place of using getdate function we can manually provide date as input.
Output:
Query 3:
-
SELECT getdate() as 'Current_date',
-
--DATEADD (datepart , number , date )
-
---For DATE
-
DATEADD (month , 3 , GETDATE() ) AS "Add 3 Months to the current date." -
,DATEADD (month , -3 , GETDATE() ) AS "Subtract 3 Months from current date."
-
,DATEADD (day , 7 , GETDATE() ) AS "Add 7 Days to the current date"
-
,DATEADD (week , 1 , GETDATE() ) AS "Add 1 Week to the current date"
-
,DATEADD (year , 1 , GETDATE() ) AS "Add 1 Year to the current date"
-
--For TIME
-
,DATEADD (hour , 3 , GETDATE() ) AS "Add 2 Hours to the current time"
-
,DATEADD (minute , -15 , GETDATE() ) AS "Subtract 15 Minutes from the current time"
Output:
