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: