DATEADD (SQL)
Synopsis
DATEADD(datePart,numUnits,date)
Description
-
DATEADD(datePart,numUnits,date) modifies a date or time expression by incrementing the specified date part by the specified number of units. If you specify negative units, then DATEADD decrements the date by that number of units.
-
If date is of type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), then DATEADD returns a timestamp of type %Library.PosixTimeOpens in a new tab.
-
If date is of any other type, then DATEADD returns a timestamp of type %Library.TimeStampOpens in a new tab in the format yyyy-mm-dd hh:mm:ss.fff.
This statement increments the current date by 5 months.
SELECT DATEADD('month',5,CURRENT_DATE)
-
DATEADD is compatible with Sybase and Microsoft SQL Server.
Arguments
datePart
The full or abbreviated name of a date or time part. You can specify datePart in uppercase or lowercase. In embedded SQL, specify datePart as a literal value or host variable. This table shows the valid date and time part names and abbreviations. It also shows by how much a single unit of that part (numUnits = 1) increments the date.
Name | Abbreviations | numUnits = 1 |
---|---|---|
year | yyyy, yy | Increments year by 1. |
quarter | qq, q | Increments month by 3. |
month | mm, m | Increments month by 1. |
week | wk, ww | Increments day by 7. |
weekday | dw, w | Increments day by 1. |
day | dd, d | Increments day by 1. |
dayofyear | dy, y | Increments day by 1. |
hour | hh, h | Increments hour by 1. |
minute | mi, n | Increments minute by 1. |
second | ss, s | Increments second by 1. |
millisecond | ms | Increments second by 0.001 (precision of 3) |
microsecond | mcs | Increments second by 0.000001 (precision of 6) |
nanosecond | ns | Increments second by 0.000000001 (precision of 9) |
Incrementing or decrementing a date part causes other date parts to be modified appropriately. For example, incrementing the hour past midnight automatically increments the day, which may in turn increment the month, and so forth.
DATEADD performs slightly different operations depending on whether you specify datePart with or without quotes:
-
Quotes: DATEADD('month',1,'2022-02-25'): datePart is treated as a literal. When processing the query, InterSystems SQL performs literal substitution, replacing the 'month' string with an input parameter, which produces a more generally reusable cached query.
-
No quotes: DATEADD(month,1,'2022-02-25'): datePart is treated as a keyword. When processing the query, InterSystems SQL does not perform literal substitution, which produces a more specific cached query.
Specifying an invalid datePart literal value generates an SQLCODE -8 error code. However, if you supply an invalid datePart value as a host variable, no SQLCODE error is issued and the DATEPART function that is called to parse datePart returns a value of NULL.
numUnits
The number of datePart units added to or subtracted from date, specified as a numeric value. DATEADD truncates numUnits to an integer. If numUnits contains no numeric parts or does not start with a numeric value, DATEADD truncates this value to 0 and returns the originally specified date.
date
The date or time expression being added to or subtracted from, specified as one of these InterSystems IRIS® data types:
-
%Date logical value (+$H), also known as $HOROLOG format.
-
%PosixTime (%Library.PosixTimeOpens in a new tab) logical value (an encoded 64-bit signed integer).
-
%TimeStamp (%Library.TimeStampOpens in a new tab) logical value (YYYY-MM-DD HH:MM:SS.FFF), also known as ODBC format.
-
%String or string-compatible value, which can be in one of these formats:
$HOROLOG Date and Time FormatFormat Example ddddd SELECT DATEADD('yy',1,'66716') ddddd,sssss SELECT DATEADD('yy',1,'66716,256') ddddd,sssss.fff SELECT DATEADD('yy',1,'66716,256.467') where:
-
ddddd is the integer number of days since December 31, 1840.
-
sssss is the integer number of seconds since the start of that day.
-
fff is the integer number of fractional seconds. If you specify fractional seconds, the returned DATEADD value also includes fractional seconds.
Date FormatFormat Example MM/DD/YY SELECT DATEADD('year',1,'12/31/99') MM/DD/YYYY SELECT DATEADD('year',1,'8/24/2022') MM-DD-YY SELECT DATEADD('year',1,'12-31-99') MM-DD-YYYY SELECT DATEADD('year',1,'8-24-2022') MM.DD.YY SELECT DATEADD('year',1,'12.31.99') MM.DD.YYYY SELECT DATEADD('year',1,'8.24.2022') Mmm DD YY SELECT DATEADD('year',1,'Dec 30 92') Mmm DD YYYY SELECT DATEADD('year',1,'January 23 2021') Mmm DD, YY SELECT DATEADD('year',1,'Dec 30, 92') Mmm DD, YYYY SELECT DATEADD('year',1,'January 23, 2021') where:
-
MM is the two-digit month.
-
DD is the two-digit number of days in the month.
-
Mmm is the spelled-out month. You can specify a minimum of three letters (for example, Mar) up to the full month name (for example, March).
-
YY and YYYY are the two-digit and four-digit forms of the year, respectively.
You can specify date as a combined date and time string. For example:
SELECT DATEADD('hh',1,'12/22/2021 8:15:23')
If you specify a time without a date, DATEADD defaults to date 01/01/1900.
Time FormatFormat Example HH: SELECT DATEADD('hour',1,'10:') HH:MM SELECT DATEADD('mi',1,'10:30') HH:MM:SS SELECT DATEADD('ss',1,'10:30:59') HH...SS.FFF SELECT DATEADD('ms',1,'10:30:59.245') HH...[AM|PM] SELECT DATEADD('mi',1,'10:30PM') where:
-
HH is the two-digit number of hours into the day.
-
MM is the two-digit number of minutes into the hour.
-
SS is the two-digit number of seconds into the minute.
-
FFF is the number of fractional seconds.
You can specify date as a combined date and time string. For example:
SELECT DATEADD('hh',1,'12/22/2021 8:15:23')
If you specify a date without a time, DATEADD defaults to time 00:00:00.
-
The date argument has these restrictions and behaviors:
-
The date string must be complete and properly formatted with the appropriate number of elements and digits for each element and the appropriate separator character. Years must be specified as four digits. If you omit the date portion of an input value, DATEADD defaults to '1900-01-01'.
-
Date and time values must be within the valid range:
-
Years — 0001 through 9999
-
Months — 1 through 12
-
Days — 1 through 31
-
Hours — 00 through 23
-
Minutes — 0 through 59
-
Seconds — 0 through 59
The number of days in a month must match the month and year. For example, the date '02-29' is valid only if the specified year is a leap year.
-
-
In date values less than 10 (month and day) a leading zero in optional. Other non-canonical integer values are not permitted. For example, a Day value of '07' or '7' is valid, but '007', '7.0', or '7a' are not valid.
-
Time values are optional. If date specifies an incomplete time, zeros are supplied for the unspecified parts.
-
An hour value less than 10 must include a leading zero.
Examples
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
Alternatives
You can perform time and date modifications using the TIMESTAMPADD ODBC scalar function.
To perform equivalent timestamp conversions in ObjectScript, use the DATEADD()Opens in a new tab method:
$SYSTEM.SQL.Functions.DATEADD(datePart,numUnits,date)
See Also
-
DATEDIFF function
-
DATENAME function
-
DATEPART function
-
TIMESTAMPADD function
-
TIMESTAMPDIFF function