docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Functions  /  DATEADD


InterSystems SQL Reference
DATEADD
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


A date/time function that returns a timestamp calculated by adding or subtracting a number of date part units (such as hours or days) to a date or timestamp.
Synopsis
DATEADD(datepart,integer-exp,date-exp)
Arguments
datepart The name (or abbreviation) of a date or time part. This name can be specified in uppercase or lowercase, with or without enclosing quotes. The datepart can be specified as a literal or a host variable.
integer-exp A numeric expression of any number type. The value is truncated to an integer (positive or negative). The value indicates the number of datepart units that will be added to (or subtracted from) date-exp.
date-exp The date/time expression to be modified. This can be a date string, or a timestamp string (either %PosixTime or %TimeStamp data type), or a function such as CURRENT_DATE. The value returned is always a timestamp, in either %PosixTime or %TimeStamp data type format.
Description
The DATEADD function modifies a date/time expression by incrementing the specified date part by the specified number of units. For example, if datepart is 'month' and integer-exp is 5, DATEADD increments date-exp by five months. You can also decrement a date part by specifying a negative integer for integer-exp.
The calculated date is returned as a complete date/time expression (a timestamp). The returned data type depends on the data type of date-exp. If date-exp is %Library.PosixTime (an encoded 64-bit signed integer), DATEADD returns data type %Library.PosixTime. Otherwise, DATEADD returns data type %Library.TimeStamp (yyyy-mm-dd hh:mm:ss.fff).
DATEADD always returns a valid date, taking into account the number of days in a month, and calculating for leap year. For example, incrementing January 31 by one month returns February 28 (the highest valid date in the month), unless the specified year is a leap year, in which case it returns February 29. Incrementing a leap year date of February 29 by one year returns February 28. Incrementing a leap year date of February 29 by four years returns February 29.
If you specify a date-exp that includes fractional seconds, the returned value also includes fractional seconds. If you omit the time portion of date-exp, DATEADD returns a default time of 00:00:00. If you omit the date portion of date-exp, DATEADD returns a default date of 1900–01–01.
DATEADD and TIMESTAMPADD handle quarters (3–month intervals); DATEDIFF and TIMESTAMPDIFF do not handle quarters.
Similar time/date modification operations can be performed using the TIMESTAMPADD ODBC scalar function.
This function can also be invoked from ObjectScript using the DATEADD() method call:
$SYSTEM.SQL.DATEADD(datepart,integer-exp,date-exp)
Datepart Argument
The datepart argument can be one of the following date/time components, either the full name (the Date Part column) or its abbreviation (the Abbreviation column). These datepart component names and abbreviations are not case-sensitive.
Date Part Abbreviations integer-exp = 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 Increments day by 1.
day dd, d Increments day by 1.
dayofyear dy, y Increments day by 1.
hour hh Increments hour by 1.
minute mi, n Increments minute by 1.
second ss, s Increments second by 1.
millisecond ms Increments by .001 of a second.
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.
A datepart can be specified as a quoted string or without quotes. These syntax variants perform slightly different operations:
If you specify an invalid datepart value as a literal, an SQLCODE -8 error code is issued. However, if you supply an invalid datepart value as a host variable, no SQLCODE error is issued and the DATEPART function returns a value of NULL.
Date Expression Formats
The date-exp argument can be in any of the following formats, and may include or omit fractional seconds:
The InterSystems IRIS %String (or compatible) value can be in any of the following formats:
Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).
Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
If the year is given as two digits, InterSystems IRIS checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the legacy documentation available at http://docs.intersystems.com/priordocexcerpts. For information on setting the sliding window for the current process, see the documentation for the ObjectScript $ZDATE, $ZDATEH, $ZDATETIME and $ZDATETIMEH functions.
Note that DATEADD is provided for Sybase and Microsoft SQL Server compatibility.
Range and Value Checking
DATEADD performs the following checks on input values. If a value fails a check, the null string is returned.
Examples
The following example adds 1 week to the specified date:
SELECT DATEADD('week',1,'2018-02-26') AS NewDate
it returns 2018-03-05 00:00:00, because adding 1 week adds 7 days. Note that DATEADD supplies the omitted time portion.
The following example adds 5 months to the timestamp:
SELECT DATEADD(MM,5,'2017-11-26 12:00:00') AS NewDate
it returns 2018-04-26 12:00:00 because adding 5 months also increments the year.
The following example also adds 5 months to the timestamp:
SELECT DATEADD('mm',5,'2018-01-31 12:00:00') AS NewDate
it returns 2018-06-30 12:00:00. Here DATEADD modified the day value as well as the month, because simply incrementing the month would result in June 31, which is an invalid date.
The following example adds 45 minutes to the timestamp:
SELECT DATEADD(MI,45,'2018-02-26 12:00:00') AS NewTime
it returns 2018-02-26 12:45:00.
The following example also adds 45 minutes to the timestamp, but in this case the addition increments the day, which increments the month:
SELECT DATEADD('mi',45,'2018-02-28 23:30:00') AS NewTime
it returns 2018-03-01 00:15:00.
The following example decrements the original timestamp by 45 minutes:
SELECT DATEADD(N,-45,'2018-01-01 00:10:00') AS NewTime
it returns 2017-12-31 23:25:00.
The following example adds 60 days to the current date, adjusting for the varying lengths of months:
SELECT DATEADD(D,60,CURRENT_DATE) AS NewDate
In the following example, the first DATEADD adds 92 days to the specified date, the second DATEADD adds 1 quarter to the specified date:
SELECT DATEADD('dd',92,'2018-12-20') AS NewDateD,
       DATEADD('qq',1,'2018-12-20') AS NewDateQ
The first returns 2019-03-22 00:00:00; the second returns 2019-03-20 00:00:00. Incrementing by a quarter increments the month field by 3, and, when needed, increments the year field. It also corrects for the maximum number of days for a given month.
The above examples all use date part abbreviations. However, you can also specify the date part by its full name, as in this example:
SELECT DATEADD('day',92,'2018-12-20') AS NewDate
it returns 2019-03-22 00:00:00.
The following Embedded SQL example uses host variables to perform the same DATEADD operation as the previous example:
  SET x="day"
  SET datein="2019-12-20"
  &sql(SELECT DATEADD(:x,92,:datein)
       INTO :dateout)
  WRITE "in:  ",datein,!,"out: ",dateout
See Also