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.
||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.
||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.
||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.
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
by five months. You can also decrement a date part by specifying a negative integer for integer-exp
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
returns a default time of 00:00:00. If you omit the date portion of date-exp
returns a default date of 19000101.
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()
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.
||integer-exp = 1
||Increments year by 1.
||Increments month by 3.
||Increments month by 1.
||Increments day by 7.
||Increments day by 1.
||Increments day by 1.
||Increments day by 1.
||Increments hour by 1.
||Increments minute by 1.
||Increments second by 1.
||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.
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
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:
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
is a slash (/), hyphen (-), or period (.).
Note that DATEADD
is provided for Sybase and Microsoft SQL Server compatibility.
Range and Value Checking
performs the following checks on input values. If a value fails a check, the null string is returned.
A 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.
Date values must be within a valid range. Years: 1841 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 0 through 23. Minutes: 0 through 59. Seconds: 0 through 59.
The incremented year value returned must be within the range 1841 through 9999. Incrementing beyond this range returns <null>.
The number of days in a month must match the month and year. For example, the date '0229' is only valid if the specified year is a leap year.
Date values less than 10 may include or omit a leading zero. Other non-canonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.
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:
WRITE "in: ",datein,!,"out: ",dateout