DATEADD

Description

Increments a DATE, TIME, TIMETZ, or TIMESTAMP value by a specified interval.

(RedShift SQL Language Reference DATEADD function)

The DATEADD function in Amazon Redshift adds a specified time interval to a date or timestamp. Where datepart is the type of interval (like 'day' or 'month'), interval is the number of units to add (positive or negative), and date is the original date.

Valid datepart in Snowflake for DATEADD:

• microsecond, microseconds

• millisecond, milliseconds

• second, seconds

• minute, minutes

• hour, hours

• day, days

• week

• month, months

• quarter, quarters

• year, years

Unsupported formats in Snowflake for DATEADD:

• weeks

• decade, decades

• century, centuries

• millennium, millennia

Grammar Syntax

DATEADD(datepart, interval, date)

Sample Source Patterns

Supported date parts:

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT dateadd(year, 1, '2024-02-29') AS D1, dateadd(year, 1, '2023-02-28') AS D2
        date_add('year'::text, 1::bigint, '2024-02-29 00:00:00'::timestamp without time zone) AS D3;

Result

D1	D2	D3
2025-03-01 00:00:00.000000	2024-02-28 00:00:00.000000	2025-03-01 00:00:00.000000

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
DATEADD(day, 1, dateadd(year, 1, '2024-02-29')) AS D1,
DATEADD(year, 1, '2023-02-28') AS D2,
DATEADD('year', 1, '2024-02-29 00:00:00':: TIMESTAMP_NTZ) AS D3;

Result

D1	D2	D3
2025-03-01 00:00:00.000	2024-02-28 00:00:00.000	2025-03-01 00:00:00.000000

Unsupported date parts

This transformation is performed in order to emulate Redshift behavior for the following date parts

• weeks is converted to its equivalent supported date part, week.

The following date parts are transformed to their equivalent in years:

• decade, decades : converted to year times ten.

• century, centuries: converted to year times one hundred.

• millennium, millennia: converted to year times one thousand.

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT DATEADD(weeks, 1, '2023-02-28') AS wks,
        DATEADD(decade, 1, '2023-02-28') AS dec,
        DATEADD(century, 1, '2023-02-28') AS cen,
        DATEADD(millennium, 1, '2023-02-28') AS mill;
        

SELECT 
    DATEADD(millennium, num_interval, '2023-02-28') AS result
FROM (
    SELECT 5 AS num_interval
);

Result

wks	dec	cen	mill
2023-03-07 00:00:00.000000	2033-02-28 00:00:00.000000	2123-02-28 00:00:00.000000	3023-02-28 00:00:00.000000

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    DATEADD(week, 1, '2023-02-28') AS wks,
    DATEADD(YEAR, 1 * 10, '2023-02-28') AS dec,
    DATEADD(YEAR, 1 * 100, '2023-02-28') AS cen,
    DATEADD(YEAR, 1 * 1000, '2023-02-28') AS mill;


SELECT
    DATEADD(YEAR, num_interval * 1000, '2023-02-28') AS result
FROM (
    SELECT 5 AS num_interval
);

Result

wks	dec	cen	mill
2023-03-07 00:00:00.000000	2033-02-28 00:00:00.000000	2123-02-28 00:00:00.000000	3023-02-28 00:00:00.000000

Notes

In Amazon Redshift, when you use DATEADD to add years to February 29 of a leap year, it rolls over to March 1 of the following year because the next year is not a leap year. Redshift handles date arithmetic by moving to the nearest valid date. Since February 29 does not exist in non-leap years, it defaults to March 1. For example, adding one year to February 29, 2020, results in March 1, 2021.

Related EWIs

There are no known issues.