DATE_PART

Date function

Description

DATE_PART extracts date part values from an expression. DATE_PART is a synonym of the PGDATE_PART function.

(RedShift SQL Language Reference DATE_PART function)

Valid datepart in Snowflake for DATE_PART:

• second, seconds

• minute, minutes

• hour, hours

• day, days

• week

• dayofweek

• dayofyear

• month, months

• quarter, quarters

• year, years

• epoch

Invalid formats in Snowflake for DATE_PART:

• microsecond, microseconds

• millisecond, milliseconds

• weeks

• decade, decades

• century, centuries

• millennium, millennia

Grammar Syntax

{PGDATE_PART | DATE_PART}(datepart, {date|timestamp})

This function is partially supported by Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT DATE_PART(minute, timestamp '2023-01-04 04:05:06.789') AS dateMinute,
    PGDATE_PART(dayofweek, timestamp '2022-05-02 04:05:06.789') AS dateDayofweek,
    "DATE_PART"('month', date '2022-05-02') AS dateMonth,
    pgdate_part('weeks'::text, '2023-02-28'::date::timestamp without time zone) AS wks;
    
SELECT DATE_PART(weeks, date '2023-02-28') AS wks,
    DATE_PART(decade, date '2023-02-28') AS dec,
    PGDATE_PART(century, date '2023-02-28') AS cen;

Result

dateMinute	dateDayofweek	dateMonth	wks
5	1	5	9

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    DATE_PART(minute, timestamp '2023-01-04 04:05:06.789') AS dateMinute,
    DATE_PART(dayofweek, timestamp '2022-05-02 04:05:06.789') AS dateDayofweek,
    DATE_PART('month', date '2022-05-02') AS dateMonth,
    DATE_PART(week, '2023-02-28'::date:: TIMESTAMP_NTZ) AS wks;

SELECT
    DATE_PART(week, date '2023-02-28') AS wks,
    !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - decade FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!
    DATE_PART(decade, date '2023-02-28') AS dec,
    !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - century FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!
    DATE_PART(century, date '2023-02-28') AS cen;

Result

dateMinute	dateDayofweek	dateMonth	wks
5	1	5	9

Know Issues

No issues were found.

Related EWIs

• SSC-EWI-PGOOO5: The current date/numeric format may have a different behavior in Snowflake.