DATE_TRUNC

Date function

Description

The DATE_TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, day, or month.

(Redshift SQL Language Reference DATE_TRUNC function).

In Snowflake this function truncates a DATE, TIME, or TIMESTAMP value to the specified precision.

Valid <datepart> in Snowflake:

• microsecond, microseconds

• millisecond, milliseconds

• second, seconds

• minute, minutes

• hour, hours

• day, days

• week

• month, months

• quarter, quarters

• year, years

Invalid formats in Snowflake:

• Weeks

• decade, decades

• century, centuries

• millennium, millennia

This function is fully supported in Snowflake.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

DATE_TRUNC('datepart', timestamp)

Sample Source Patterns

Supported date parts

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT
    DATE_TRUNC('second', TIMESTAMP '2024-02-02 04:05:06.789') AS sec,
    DATE_TRUNC('hours', TIMESTAMP '2024-02-02 04:05:06.789') AS hrs,
    DATE_TRUNC('week', TIMESTAMP '2024-02-02 04:05:06.789') AS wk, 
    "DATE_TRUNC"('month', TIMESTAMP '2024-02-02 04:05:06.789') AS mth,
    "date_trunc"('quarters', TIMESTAMP '2024-02-02 04:05:06.789') AS qtr,
    date_trunc('second'::text, '2024-02-02 04:05:06.789'::timestamp without time zone) AS sec2;

Result

SEC	HRS	WK	MTH	QTR	SEC2
2024-02-02 04:05:06.000000	2024-02-02 04:00:00.000000	2024-01-29 00:00:00.000000	2024-02-01 00:00:00.000000	2024-01-01 00:00:00.000000	2024-02-02 04:05:06.000000

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    DATE_TRUNC('second', TIMESTAMP '2024-02-02 04:05:06.789') AS sec,
    DATE_TRUNC('hours', TIMESTAMP '2024-02-02 04:05:06.789') AS hrs,
    DATE_TRUNC('week', TIMESTAMP '2024-02-02 04:05:06.789') AS wk,
    DATE_TRUNC('month', TIMESTAMP '2024-02-02 04:05:06.789') AS mth,
    DATE_TRUNC('quarters', TIMESTAMP '2024-02-02 04:05:06.789') AS qtr,
    date_trunc('second','2024-02-02 04:05:06.789':: TIMESTAMP_NTZ) AS sec2;

Result

SEC	HRS	WK	MTH	QTR	SEC2
2024-02-02 04:05:06.000	2024-02-02 04:00:00.000	2024-01-29 00:00:00.000	2024-02-01 00:00:00.000	2024-01-01 00:00:00.000	2024-02-02 04:05:06.000000

Invalid date parts

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

• decade, decades

• century, centuries

• millennium, millennia

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT
    DATE_TRUNC('weeks', TIMESTAMP '1990-02-02 04:05:06.789') AS wks,
    DATE_TRUNC('decade', TIMESTAMP '1990-02-02 04:05:06.789') AS dec,    
    DATE_TRUNC('century', TIMESTAMP '1990-02-02 04:05:06.789') AS c,        
    DATE_TRUNC('millennium', TIMESTAMP '1990-02-02 04:05:06.789') AS m;

Result

WKS	DEC	C	M
1990-01-29 00:00:00.000000	1990-01-01 00:00:00.000000	1901-01-01 00:00:00.000000	1001-01-01 00:00:00.000000

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT
    DATE_TRUNC(week, TIMESTAMP '1990-02-02 04:05:06.789') AS wks,
    DATEADD(year, -(EXTRACT(year FROM TIMESTAMP '1990-02-02 04:05:06.789')) % 10, DATE_TRUNC(year, TIMESTAMP '1990-02-02 04:05:06.789')) AS dec,
    DATEADD(year, -(EXTRACT(year FROM TIMESTAMP '1990-02-02 04:05:06.789') - 1) % 100, DATE_TRUNC(year, TIMESTAMP '1990-02-02 04:05:06.789')) AS c,
    DATEADD(year, -(EXTRACT(year FROM TIMESTAMP '1990-02-02 04:05:06.789') - 1) % 1000, DATE_TRUNC(year, TIMESTAMP '1990-02-02 04:05:06.789')) AS m;

Result

WKS	DEC	C	M
1990-01-29 00:00:00.000	1990-01-01 00:00:00.000	1901-01-01 00:00:00.000	1001-01-01 00:00:00.000

For more information please refer to the following documentation in Snowflake:

• DATEADD

• EXTRACT

Known Issues

In Amazon Redshift, the default precision for timestamps is 6 digits (microseconds), while in Snowflake, the default precision is 9 digits (nanoseconds). Due to these differences in precision, it’s important to consider your specific needs when working with timestamps. If you require different precision in either platform, you can use the following options.

1. Use ALTER SESSION:

   --This example is for 2 digits for precision (FF2).
   ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';

Please note that depending on the data type used to store the value obtained with DATE_TRUNC(), there may be limitations in precision that could result in a loss of accuracy.

Related EWIs

There are no known issues.