Date Trunc

Date/Time Function

Description

Truncate to specified precision.

Click here to navigate to the PostgreSQL docs page for this syntax.

The function Date_trunc() is supported on snowflake but some precision formats are not supported by Snowflake.

Grammar Syntax

DATE_TRUNC ( text, timestamp ) 

Sample Source Patterns

PostgreSQL

Code

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');

Result

2001-01-01 00:00:00

Snowflake

Code

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');

Result

2001-01-01 00:00:00.000

Unsupported Precision Formats

PostgreSQL

Code 1

SELECT 
   DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

Snowflake

Code 1

SELECT
-- ** MSC-ERROR - MSC-PG0010 - DECADE FORMAT IS NOT SUPPORTED IN SNOWFLAKE. **
--   DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30')
                                                        ,
-- ** MSC-ERROR - MSC-PG0010 - CENTURY FORMAT IS NOT SUPPORTED IN SNOWFLAKE. **
--   DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30')
                                                         ,
-- ** MSC-ERROR - MSC-PG0010 - MILLENNIUM FORMAT IS NOT SUPPORTED IN SNOWFLAKE. **
--   DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30')
;

Related EWIs

1. MSC-PG0010: PRECISION FORMAT IS NOT SUPPORTED IN SNOWFLAKE.