Add Varying Time Units to Dates
This statement adds 1 week to the specified date. It returns 2022-03-05 00:00:00, because adding 1 week adds 7 days. DATEADD supplies the omitted time portion.
SELECT DATEADD('week',1,'2022-02-26') AS NewDate
The statement adds 5 months to the specified timestamp and returns 2022-04-26 12:00:00. DATEADD modifies both the month and year, because adding 5 months also increments the year.
SELECT DATEADD(MM,5,'2021-11-26 12:00:00') AS NewDate
This statement also adds 5 months to the timestamp and returns 2021-06-30 12:00:00. DATEADD modifies both the day and month, because incrementing only the month results in an invalid date of June 31.
SELECT DATEADD('mm',5,'2021-01-31 12:00:00') AS NewDate
This statement adds 45 minutes to the timestamp and returns 2022-02-26 12:45:00.
SELECT DATEADD(MI,45,'2022-02-26 12:00:00') AS NewTime
This statement also adds 45 minutes to the timestamp, but in this case the addition increments the day, which increments the month. It returns 2022-03-01 00:15:00.
SELECT DATEADD('mi',45,'2022-02-28 23:30:00') AS NewTime
This statement decrements the original timestamp by 45 minutes and returns 2021-12-31 23:25:00.
SELECT DATEADD(N,-45,'2022-01-01 00:10:00') AS NewTime
This statement adds 60 days to the current date, adjusting for the varying lengths of months.
SELECT DATEADD(D,60,CURRENT_DATE) AS NewDate
The first DATEADD of this statement adds 92 days to the specified date and returns 2022-03-22 00:00:00. The second DATEADD adds 1 quarter to the specified date and returns 2022-03-20 00:00:00. Incrementing by a quarter increments the month field by 3. If necessary, DATEADD also increments the year field and corrects for the maximum number of days for a given month.
SELECT DATEADD('dd',92,'2021-12-20') AS NewDateD,
DATEADD('qq',1,'2021-12-20') AS NewDateQ
The previous statements all use date part abbreviations. However, you can also specify the date part by its full name. For example, this statement adds 92 days to the date and returns 2022-03-22 00:00:00.
SELECT DATEADD('day',92,'2021-12-20') AS NewDate
This Embedded SQL code uses host variables to perform the same DATEADD operation as the previous SQL statement.
set datePart = "day"
set numUnits = 92
set dateIn = "2021-12-20"
&sql(SELECT DATEADD(:datePart,:numUnits,:dateIn) INTO :dateOut)
write "in: ",dateIn,!,"out: ",dateOut