DATEDIFF

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 DATEDIFF:

• 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

DATEDIFF( 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 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;
    

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

Result

year	month	day
10	131	4016

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;


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
10	131	4016

week	century	decade
574	10	100

Know Issues

No issues were found.

Related EWIs

There are no known issues.