- Categories:
DATEADD¶
Adds the specified value for the specified date or time part to a date, time, or timestamp.
- Aliases:
- See also:
Syntax¶
DATEADD( <date_or_time_part>, <value>, <date_or_time_expr> )
Arguments¶
date_or_time_part
This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be
DAY
. This unit of measure must be one of the values listed in Supported date and time parts.value
This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
date_or_time_expr
date_or_time_expr
must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be'2018-08-01'::DATE
.If the data type is TIME, then the
date_or_time_part
must be in units of hours or smaller, not days or bigger.If the input data type is DATE, and the
date_or_time_part
is hours or smaller, the input value will not be rejected, but instead will be treated as a TIMESTAMP with hours, minutes, seconds, and fractions of a second all initially set to 0 (e.g. midnight on the specified date).
Returns¶
If date_or_time_expr
is a time:
The return data type is a time.
If date_or_time_expr
is a timestamp:
The return data type is a timestamp.
If date_or_time_expr
is a date:
If
date_or_time_part
isday
or larger (e.g.month
,year
), the function returns a DATE value.If
date_or_time_part
is smaller than a day (e.g.hour
,minute
,second
), the function returns a TIMESTAMP_NTZ value, with 00:00:00.000 as the starting time for the date.
Usage notes¶
When
date_or_time_part
isyear
,quarter
, ormonth
(or any of their variations), if the result month has fewer days than the original day of the month, the result day of the month might be different from the original day.
Examples¶
Add years to a date:
SELECT TO_DATE('2013-05-08') AS v1, DATEADD(year, 2, TO_DATE('2013-05-08')) AS v;Output:
+------------+------------+ | V1 | V | |------------+------------| | 2013-05-08 | 2015-05-08 | +------------+------------+
Add 2 years and 2 hours to a date:
Initial setup of this example and some subsequent examples:
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9'; CREATE TABLE datetest (d date); INSERT INTO datetest VALUES ('2013-04-05');Query to add 2 years and 2 hours to a date:
SELECT d AS "DATE", dateadd(year, 2, d) AS add_2_years, dateadd(hour, 2, d) AS add_2_hours FROM datetest;Output:
+------------+-------------+-------------------------+ | DATE | ADD_2_YEARS | ADD_2_HOURS | |------------+-------------+-------------------------| | 2013-04-05 | 2015-04-05 | 2013-04-05 02:00:00.000 | +------------+-------------+-------------------------+
Add hours to a timestamp:
SELECT TO_TIMESTAMP_LTZ('2013-05-08 11:22:33.444') AS v1, DATEADD(HOUR, 2, TO_TIMESTAMP_LTZ('2013-05-08 11:22:33.444')) AS v;Output:
+-------------------------------+-------------------------------+ | V1 | V | |-------------------------------+-------------------------------| | 2013-05-08 11:22:33.444000000 | 2013-05-08 13:22:33.444000000 | +-------------------------------+-------------------------------+
Add a month to a date in a month with the same or more days than the resulting month. For example, if today is January 31, adding a month should not give us “February 31”.
SELECT DATEADD(MONTH, 1, '2000-01-31'::DATE) AS DIFFERENT_DAY;Output:
+---------------+ | DIFFERENT_DAY | |---------------| | 2000-02-29 | +---------------+
Add a month to a date in a month with fewer days than the resulting month. Adding a month to February 29 should give us March 29, which is safe.
SELECT DATEADD(MONTH, 1, '2000-02-29'::DATE) AS RESULT;Output:
+------------+ | RESULT | |------------| | 2000-03-29 | +------------+