A general date/time function
that returns a date calculated by adding a certain number of date parts (such
as hours or days) to a specified timestamp.
Synopsis
DATEADD(datepart,integer-exp,date-exp)
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 TIMESTAMP.
DATEADD always
returns a complete date/time expression in the format:
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.
Similar time/date modification operations can be performed using the
TIMESTAMPADD ODBC scalar function.
The
datepart parameter can be one of the following
date/time components, either the full name (the Date Part column) or its abbreviation
(the Abbreviation column):
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 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.
The
date-exp parameter can be in any of the following
formats:
-
A Caché %Date logical value (+$H)
-
A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
-
A Caché %String (or compatible) value
The Caché %String (or compatible) value can be in any of the
following 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
(.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
If the year is given as two digits, the %DATE utility's sliding-date
window is used (if applicable) to determine the century.
Note that
DATEADD is provided for Sybase and Microsoft
SQL Server compatibility.
DATEADD 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 though 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 '02–29' 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 original timestamp:
SELECT DISTINCT DATEADD('week',1,'1999-12-20') AS NewDate
FROM sample.person
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 DISTINCT DATEADD('mm',5,'1999-12-20 12:00:00') AS NewDate
FROM sample.person
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 DISTINCT DATEADD('mm',5,'1999-01-31 12:00:00') AS NewDate
FROM sample.person
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 DISTINCT DATEADD('mi',45,'1999-12-20 12:00:00') AS NewTime
FROM sample.person
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 DISTINCT DATEADD('mi',45,'1999-12-20 23:30:00') AS NewTime
FROM sample.person
it returns 1999-12-21 00:15:00.
The following example decrements the original timestamp by 45 minutes:
SELECT DISTINCT DATEADD('mi',-45,'1999-12-20 12:00:00') AS NewTime
FROM sample.person
it returns 1999-12-20 11:15:00.
The first of the following examples add 92 days to the timestamp, and
the second example adds 1 quarter to the timestamp:
SELECT DISTINCT DATEADD('dd',92,'1999-12-20') AS NewDate
FROM sample.person
SELECT DISTINCT DATEADD('qq',1,'1999-12-20') AS NewDate
FROM sample.person
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 DISTINCT DATEADD('day',92,'1999-12-20') AS NewDate
FROM sample.person
it returns 2000-03-21 00:00:00.