Caché SQL Reference
DATEADD
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
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, or a function such as CURRENT_DATE. The value returned is always a timestamp, in %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). 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 Caché %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, Caché 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 chapter in Using InterSystems Documentation. 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,'1999-12-20') AS NewDate
 
it returns 1999-12-27 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 original timestamp:
SELECT DATEADD(MM,5,'1999-12-20 12:00:00') AS NewDate
 
it returns 2000-05-20 12:00:00 because adding 5 months also increments the year.
The following example also adds 5 months to the original timestamp:
SELECT DATEADD('mm',5,'1999-01-31 12:00:00') AS NewDate
 
it returns 1999-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 original timestamp:
SELECT DATEADD(MI,45,'1999-12-20 12:00:00') AS NewTime
 
it returns 1999-12-20 12:45:00.
The following example also adds 45 minutes to the original timestamp, but in this case the addition increments the date:
SELECT DATEADD('mi',45,'1999-12-20 23:30:00') AS NewTime
 
it returns 1999-12-21 00:15:00.
The following example decrements the original timestamp by 45 minutes:
SELECT DATEADD(N,-45,'1999-12-20 12:00:00') AS NewTime
 
it returns 1999-12-20 11:15: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,'1999-12-20') AS NewDateD,
       DATEADD('qq',1,'1999-12-20') AS NewDateQ
 
The first returns 2000-03-21 00:00:00; the second returns 2000-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,'1999-12-20') AS NewDate
 
it returns 2000-03-21 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="1999-12-20"
  &sql(SELECT DATEADD(:x,92,:datein)
       INTO :dateout)
  WRITE "in:  ",datein,!,"out: ",dateout
 
See Also