SSC-EWI-PG0005

The current date/numeric format may have a different behavior in Snowflake.

Severity

Medium

Description

This error is added because Snowflake does not support date/numeric formats in some functions as is supported in the source language.

The following format elements are the ones that may behave differently in Snowflake:

Date / Time

Format Element	Description
HH	Hour of day (01–12).
MS	Millisecond (000–999).
US	Microsecond (000000–999999).
SSSS, SSSSS	Seconds past midnight (0–86399).
Y,YYY	Year (4 or more digits) with comma.
YYY	Last 3 digits of year.
Y	Last digit of year.
IYYY	ISO 8601 week-numbering year(4 or more digits).
IYY	Last 3 digits of ISO 8601 week-numbering year.
IY	Last 2 digits of ISO 8601 week-numbering year.
I	Last digit of ISO 8601 week-numbering year.
BC, bc, AD or ad	Era indicator (without periods).
B.C., b.c., A.D. or a.d.	Era indicator (with periods).
MONTH	Full upper case month name (blank-padded to 9 chars).
Month	Full capitalized month name (blank-padded to 9 chars).
month	Full lower case month name (blank-padded to 9 chars).
DAY	Full upper case day name (blank-padded to 9 chars).
Day	Full capitalized day name (blank-padded to 9 chars).
day	Full lower case day name (blank-padded to 9 chars).
DDD	Day of year (001–366).
IDDD	Day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week).
D	Day of the week, Sunday (1) to Saturday (7).
ID	ISO 8601 day of the week, Monday (1) to Sunday (7).
W	Week of month (1–5) (the first week starts on the first day of the month).
WW	Week number of year (1–53) (the first week starts on the first day of the year).
IW	Week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1).
CC	Century (2 digits) (the twenty-first century starts on 2001-01-01).
J	Julian Date.
Q	Quarter.
RM	Month in upper case Roman numerals (I–XII; I=January).
rm	Month in lower case Roman numerals (i–xii; i=January).
TZ	Upper case time-zone abbreviation (only supported in to_char).
tz	Lower case time-zone abbreviation (only supported in to_char).
TZH	Time-zone hours.
TZM	Time-zone minutes.
OF	Time-zone offset from UTC (only supported in to_char).
FM prefix	Fill mode (suppress leading zeroes and padding blanks).
TH suffix	Upper case ordinal number suffix.
th suffix	Lower case ordinal number suffix.
FX prefix	Fixed format global option (see usage notes).
TM prefix	Translation mode (use localized day and month names based on lc_time).
SP suffix	Spell mode.

For more information please refer to PostgreSQL Date/Time formats.

The transformation of the TO_CHAR function supports most of this format elements, for a full list of suppported format elements and their equivalent mappings please refer to the Translation specification

Numeric

Pattern	Description
PR	negative value in angle brackets
RN	Roman numeral (input between 1 and 3999)
TH or th	ordinal number suffix
V	shift specified number of digits (see notes)
EEEE	exponent for scientific notation

For more information please refer to PostgreSQL Numeric formats.

Code Example

Input Code:

PostgreSQL

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');

Output Code:

Snowflake

SELECT
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - DECADE FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - CENTURY FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - MILLENNIUM FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

Recommendations

• If you need more support, you can email us at [e-mail]