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:

Redshift	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

Query

SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH:MI:SS.MS') AS col1;

Result

+----------------------+
|col1                  |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+

Snowflake

Query

SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH12:MI:SS.FF3') AS col1;

Result

+----------------------+
|col1                  |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+

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

Query

SELECT TO_CHAR(DATE '2025-07-05', '"Today is " Month DAY DD, "it belongs to the week " IW') AS result;

Result

+-------------------------------------------------------------+
|result                                                       |
+-------------------------------------------------------------+
|Today is  July      SATURDAY  05, it belongs to the week  27 |
+-------------------------------------------------------------+

Snowflake

Query

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;

Result

+-------------------------------------------------------------+
|result                                                       |
+-------------------------------------------------------------+
|Today is  July      SATURDAY  05, it belongs to the week  27 |
+-------------------------------------------------------------+

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

Query

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;

Result

+-----------------+-------------------+-------------------+
|result1          |result2            |result3            |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+

Snowflake

Query

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;

Result

+-----------------+-------------------+-------------------+
|result1          |result2            |result3            |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+

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

1. SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
2. SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied