Syntax¶

TIMEADD(<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 youwant to add two days, then specifyday. This unit of measure mustbe one of the values listed inSupported date and time parts.

value

This is the number of units of time that you want to add. For example,if the units of time isday, and you want to add two days, specify2.If you want to subtract two days, specify-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 two days to August 1, 2024, then specify'2024-08-01'::DATE.

If the data type is TIME, then thedate_or_time_partmust be in units of hours or smaller, not days or bigger.

If the input data type is DATE, and thedate_or_time_part is hoursor smaller, the input value will not be rejected, but instead will betreated as a TIMESTAMP with hours, minutes, seconds, and fractions ofa second all initially set to 0 (e.g. midnight on the specified date).

Returns¶

Ifdate_or_time_expr is a time, then the return data type is a time.

Ifdate_or_time_expr is a timestamp, then the return data type is a timestamp.

Ifdate_or_time_expr is a date:

• Ifdate_or_time_part isday or larger (for example,month,year),the function returns a DATE value.

• Ifdate_or_time_part is smaller than a day (for example,hour,minute,second), the function returns a TIMESTAMP_NTZ value, with00:00:00.000 as the startingtime for the date.

Usage notes¶

Whendate_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 mightbe different from the original day.

Examples¶

The TIMEADD and TIMESTAMPADD functions are aliases for the DATEADD function. You can use any of these threefunctions in the examples to return the same results.

Add years to a date:

SELECTTO_DATE('2022-05-08')ASoriginal_date,DATEADD(year,2,TO_DATE('2022-05-08'))ASdate_plus_two_years;

+---------------+---------------------+| ORIGINAL_DATE | DATE_PLUS_TWO_YEARS ||---------------+---------------------|| 2022-05-08    | 2024-05-08          |+---------------+---------------------+

Subtract years from a date:

SELECTTO_DATE('2022-05-08')ASoriginal_date,DATEADD(year,-2,TO_DATE('2022-05-08'))ASdate_minus_two_years;

+---------------+----------------------+| ORIGINAL_DATE | DATE_MINUS_TWO_YEARS ||---------------+----------------------|| 2022-05-08    | 2020-05-08           |+---------------+----------------------+

Add two years and two hours to a date. First, set the timestamp output format, create a table,and insert data:

ALTERSESSIONSETTIMESTAMP_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9';CREATETABLEdatetest(ddate);INSERTINTOdatetestVALUES('2022-04-05');

Run a query that adds two years and two hours to a date:

SELECTdASoriginal_date,DATEADD(year,2,d)ASdate_plus_two_years,TO_TIMESTAMP(d)ASoriginal_timestamp,DATEADD(hour,2,d)AStimestamp_plus_two_hoursFROMdatetest;

+---------------+---------------------+-------------------------+--------------------------+| ORIGINAL_DATE | DATE_PLUS_TWO_YEARS | ORIGINAL_TIMESTAMP      | TIMESTAMP_PLUS_TWO_HOURS ||---------------+---------------------+-------------------------+--------------------------|| 2022-04-05    | 2024-04-05          | 2022-04-05 00:00:00.000 | 2022-04-05 02:00:00.000  |+---------------+---------------------+-------------------------+--------------------------+

Add a month to a date in a month with the same or more days than theresulting month. For example, if the date is January 31, adding a month shouldnotreturn February 31.

SELECTDATEADD(month,1,'2023-01-31'::DATE)ASdate_plus_one_month;

+---------------------+| DATE_PLUS_ONE_MONTH ||---------------------|| 2023-02-28          |+---------------------+

Add a month to a date in a month with fewer days than the resulting month.Adding a month to February 28 returns March 28.

SELECTDATEADD(month,1,'2023-02-28'::DATE)ASdate_plus_one_month;

+---------------------+| DATE_PLUS_ONE_MONTH ||---------------------|| 2023-03-28          |+---------------------+

Add hours to a time:

SELECTTO_TIME('05:00:00')ASoriginal_time,DATEADD(hour,3,TO_TIME('05:00:00'))AStime_plus_three_hours;

+---------------+-----------------------+| ORIGINAL_TIME | TIME_PLUS_THREE_HOURS ||---------------+-----------------------|| 05:00:00      | 08:00:00              |+---------------+-----------------------+