DATE_DIFF

Date function

Description

DATEDIFF returns the difference between the date parts of two date or time expressions.

(RedShift SQL Language Reference DATEDIFF function)

Valid datepart in Snowflake for DATE_DIFF:

• microsecond, microseconds

• millisecond, milliseconds

• second, seconds

• minute, minutes

• hour, hours

• day, days

• week

• month, months

• quarter, quarters

• year, years

Unsupported formats in Snowflake for DATEDIFF:

• decade, decades

• century, centuries

• millennium, millennia

Grammar Syntax

DATE_DIFF( datepart, {date|time|timetz|timestamp}, {date|time|timetz|timestamp} )

This function is fully supported in Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT DATE_DIFF(year,'2009-01-01','2019-12-31') as year,
    DATE_DIFF(month,'2009-01-01','2019-12-31') as month,
    DATE_DIFF(day,'2009-01-01','2019-12-31') as day,
    date_diff('year'::text, '2009-01-01 00:00:00'::timestamp without time zone, '2019-12-31 00:00:00'::timestamp without time zone) AS "year2";
    

SELECT DATE_DIFF(week,'2009-01-01','2019-12-31') as week,
    DATE_DIFF(century,'1009-01-01','2009-12-31') as century,
    DATE_DIFF(decade,'1009-01-01','2009-12-31') as decade;

Result

year	month	day	year2
10	131	4016	10

week	century	decade
574	10	100

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    DATEDIFF(year, '2009-01-01', '2019-12-31') as year,
    DATEDIFF(month, '2009-01-01', '2019-12-31') as month,
    DATEDIFF(day, '2009-01-01', '2019-12-31') as day,
    DATEDIFF(year, '2009-01-01 00:00:00':: TIMESTAMP_NTZ, '2019-12-31 00:00:00':: TIMESTAMP_NTZ) AS year2;


SELECT
    DATEDIFF(week,'2009-01-01','2019-12-31') as week,
    DATEDIFF(YEAR, '1009-01-01', '2009-12-31') / 100 as century,
    DATEDIFF(YEAR, '1009-01-01', '2009-12-31') / 10 as decade;

Result

year	month	day	year2
10	131	4016	10

week	century	decade
574	10	100

Know Issues

No issues were found.

Related EWIs

There are no known issues.