MSC-PG0011

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added when the date/time format present in the function to_char may have a different behavior in the output format of the function.

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

Format ElementDescription

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.

Code Example

Input Code:

SELECT  to_char(current_date, 'HH'),
        to_char(current_date, 'MS'),
        to_char(current_date, 'US'),
        to_char(current_date, 'SSSSS'),
        to_char(current_date, 'IYYY'),
        to_char(current_date, 'DD-MM-YYY'),
        to_char(current_date, 'DY-Y-MM'),
        to_char(current_date, 'DDDMMYY');

Output Code:

SELECT  to_char(current_date, 'HH') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'MS') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'US') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'SSSSS') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'IYYY') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DD-MM-YYY') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DY-Y-MM') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DDDMMYY') /*** MSC-WARNING - MSC-PG0011 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;
        

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

Recommendations

Last updated