For datetime values
Translation specification for the TO_CHAR function when transforming date or timestamp values to string
Description
The following format strings apply to functions such as TO_CHAR. These strings can contain datetime separators (such as '
-', '/', or ':') and the following "dateparts" and "timeparts". (Redshift Datetime format strings reference page)
Grammar Syntax
TO_CHAR (timestamp_expression, 'format')The following table specifies the mapping of each format element to Snowflake:
BC, AD, bc, ad (upper and lowercase era indicators)
PUBLIC.ERA_INDICATORS_UDF
B.C,. A.D., b.c., a.d. (upper and lowercase era indicators with points)
PUBLIC.ERA_INDICATORS_WITH_POINTS_UDF
CC
PUBLIC.CENTURY_UDF
YYYY and YY
Directly supported
YYY and Y
PUBLIC.YEAR_PART_UDF
Y,YYY
PUBLIC.YEAR_WITH_COMMA_UDF
IYYY
YEAROFWEEKISO
I, IY, IYY
PUBLIC.ISO_YEAR_PART_UDF
Q
QUARTER
MONTH, Month, month
PUBLIC.FULL_MONTH_NAME_UDF
MON, Mon, mon
PUBLIC.MONTH_SHORT_UDF
RM, rm
PUBLIC.ROMAN_NUMERALS_MONTH_UDF
W
PUBLIC.WEEK_OF_MONTH_UDF
WW
PUBLIC.WEEK_NUMBER_UDF
IW
WEEKISO
DAY, Day, day
PUBLIC.DAYNAME_LONG_UDF
DY, Dy, dy
PUBLIC.DAYNAME_SHORT_UDF
DDD
DAYOFYEAR
IDDD
PUBLIC.DAY_OF_YEAR_ISO_UDF
D
PUBLIC.DAY_OF_WEEK_UDF
Notes: For this UDF to work correctly the Snowflake session parameter WEEK_START should have its default value (0).
ID
DAYOFWEEKISO
J
PUBLIC.JULIAN_DAY_UDF
HH24
Directly supported
HH
HH12
HH12
Directly supported
MI
Directly supported
SS
Directly supported
MS
FF3
US
FF6
AM, PM, am, pm (upper and lowercase meridian indicators)
PUBLIC.MERIDIAN_INDICATORS_UDF
A.M., P.M., a.m., p.m. (upper and lowercase meridian indicators with points)
PUBLIC.MERIDIAN_INDICATORS_WITH_POINTS_UDF
TZ and tz
UTC and utc
Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result.
OF
+00
Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result.
SSSS
PUBLIC.SECONDS_PAST_MIDNIGHT
SP
Notes: This is a PostgreSQL template pattern modifier for "spell mode", however it does nothing on Redshift, so it is removed from the output.
FX
Notes: This is another template pattern modifier for "fixed format", however it has no use on the TO_CHAR function so it is removed.
Sample Source Patterns
Direct format elements transformation (no functions/UDFs)
The result is preserved as a single TO_CHAR function
Redshift
SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH:MI:SS.MS') AS col1;Snowflake
SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH12:MI:SS.FF3') AS col1;Format transformation using functions/UDFs
The result is a concatenation of multiple TO_CHAR, UDFs and Snowflake built-in functions that generate the equivalent string representation of the datetime value
Redshift
SELECT TO_CHAR(DATE '2025-07-05', '"Today is " Month DAY DD, "it belongs to the week " IW') AS result;Snowflake
SELECT
'Today is ' ||
TO_CHAR(DATE '2025-07-05', ' ') ||
PUBLIC.FULL_MONTH_NAME_UDF(DATE '2025-07-05', 'firstOnly') ||
' ' ||
PUBLIC.DAYNAME_LONG_UDF(DATE '2025-07-05', 'uppercase') ||
TO_CHAR(DATE '2025-07-05', ' DD, ') ||
'it belongs to the week ' ||
TO_CHAR(DATE '2025-07-05', ' ') ||
WEEKISO(DATE '2025-07-05') AS result;Quoted text
Format elements in double quoted text are added to the output directly without interpreting them, escaped double quotes are transformed to their Snowflake escaped equivalent.
Redshift
SELECT
TO_CHAR(DATE '2025-01-16', 'MM "TESTING DD" DD') AS result1,
TO_CHAR(DATE '2025-01-16', 'MM TESTING \\"DD\\" DD') AS result2,
TO_CHAR(DATE '2025-01-16', 'MM "TESTING \\"DD\\"" DD') AS result3;Snowflake
SELECT
TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING DD' || TO_CHAR(DATE '2025-01-16', ' DD') AS result1,
TO_CHAR(DATE '2025-01-16', 'MM TEST') || PUBLIC.ISO_YEAR_PART_UDF(DATE '2025-01-16', 1) || TO_CHAR(DATE '2025-01-16', 'NG ""DD"" DD') AS result2,
TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING "DD"' || TO_CHAR(DATE '2025-01-16', ' DD') AS result3;Known Issues
Template pattern modifiers not supported
The following format template modifiers:
FM (fill mode)
TH and th (uppercase and lowercase ordinal number suffix)
TM (translation mode)
Are not supported, including them in a format will generate SSC-EWI-PG0005
Input code:
SELECT TO_CHAR(CURRENT_DATE, 'FMMonth'),
TO_CHAR(CURRENT_DATE, 'DDTH'),
TO_CHAR(CURRENT_DATE, 'DDth'),
TO_CHAR(CURRENT_DATE, 'TMMonth');Output code:
SELECT
TO_CHAR(CURRENT_DATE(), 'FM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - FMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDTH') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - DDTH FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDth') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - DDth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'TM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - TMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!;Format parameter passed through variable
When the format parameter is passed as a variable instead of a string literal, the transformation of format elements can not be applied, an FDM will be added to the uses of the function warning about it.
Input code:
SELECT TO_CHAR(d, 'YYYY/MM/DD'),
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);Output code:
SELECT TO_CHAR(d, 'YYYY/MM/DD'),
--** SSC-FDM-0032 - PARAMETER 'format_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);Related EWIs
SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied
Last updated