Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

DATEADD Function

Increments a date or time stamp value by a specified interval.

Syntax

Copy
DATEADD( datepart, interval, {date|timestamp} )

This function returns a time stamp data type.

Arguments

datepart

The date part (year, month, or day, for example) that the function operates on. See Dateparts for Date or Time Stamp Functions.

interval

An integer that specified the interval (number of days, for example) to add to the target expression. A negative integer subtracts the interval.

date|timestamp

A date or timestamp column or an expression that implicitly converts to a date or time stamp. The date or time stamp expression must contain the specified date part.

Return Type

TIMESTAMP

Examples

Add 30 days to each date in November that exists in the DATE table:

Copy
select dateadd(day,30,caldate) as novplus30 from date where month='NOV' order by dateid; novplus30 --------------------- 2008-12-01 00:00:00 2008-12-02 00:00:00 2008-12-03 00:00:00 ... (30 rows)

Add 18 months to a literal date value:

Copy
select dateadd(month,18,'2008-02-28'); date_add --------------------- 2009-08-28 00:00:00 (1 row)

The default column name for a DATEADD function is DATE_ADD. The default time stamp for a date value is 00:00:00.

Add 30 minutes to a date value that does not specify a time stamp:

Copy
select dateadd(m,30,'2008-02-28'); date_add --------------------- 2008-02-28 00:30:00 (1 row)

You can name dateparts in full or abbreviate them; in this case, m stands for minutes, not months.

Usage Notes

The DATEADD(month, ...) and ADD_MONTHS functions handle dates that fall at the ends of months differently.

  • ADD_MONTHS: If the date you are adding to is the last day of the month, the result is always the last day of the result month, regardless of the length of the month. For example, April 30th + 1 month is May 31st:

    Copy
    select add_months('2008-04-30',1); add_months --------------------- 2008-05-31 00:00:00 (1 row)
  • DATEADD: If there are fewer days in the date you are adding to than in the result month, the result will be the corresponding day of the result month, not the last day of that month. For example, April 30th + 1 month is May 30th:

    Copy
    select dateadd(month,1,'2008-04-30'); date_add --------------------- 2008-05-30 00:00:00 (1 row)

The DATEADD function handles the leap year date 02-29 differently when using dateadd(month, 12,…) or dateadd(year, 1, …).

Copy
select dateadd(month,12,'2016-02-29'); date_add --------------------- 2017-02-28 00:00:00 select dateadd(year, 1, '2016-02-29'); date_add --------------------- 2017-03-01 00:00:00