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

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;

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

Snowflake

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

+----------------------+
|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

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

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

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;

+-------------------------------------------------------------+
|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

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;

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

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;

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

SSC-EWI-PG0005: 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

PreviousTO_CHAR NextIDENTITY

Last updated 8 days ago

Description

Grammar Syntax

Sample Source Patterns

Direct format elements transformation (no functions/UDFs)

Redshift

Snowflake

Format transformation using functions/UDFs

Redshift

Snowflake

Quoted text

Redshift

Snowflake

Known Issues

Template pattern modifiers not supported

Related EWIs