To Char

Data Type Formatting Function

Description

Converts a timestamp/number to string according to the given format.

Click here to navigate to the PostgreSQL docs page for this syntax.

The function To_char() is supported on Snowflake, but some formats may fail or behave differently in Snowflake.

Grammar Syntax

TO_CHAR( timestamp, text )
TO_CHAR( numeric_type, text )

Sample Source Patterns

Numeric Formatting

When a numeric format is present in the function, the EWI MSC-PG0012 will be added because this format may fail or have a different behavior. For more information, please refer to PostgreSQL Numeric Formatting.

PostgreSQL

SELECT
   to_char(-485, '999'),
   to_char(12.20, '9999'),
   to_char(20+40, '999');

Snowflake

SELECT
   to_char(-485, '999') /*** MSC-ERROR - MSC-PG0012 - THE CURRENT TO_CHAR NUMERIC FORMAT MAY FAIL OR HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
   to_char(12.20, '9999') /*** MSC-ERROR - MSC-PG0012 - THE CURRENT TO_CHAR NUMERIC FORMAT MAY FAIL OR HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
   to_char(20+40, '999') /*** MSC-ERROR - MSC-PG0012 - THE CURRENT TO_CHAR NUMERIC FORMAT MAY FAIL OR HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;

Date/Time Formatting

Some date/time formats may have a different behavior and in that case the EWI MSCINF0041 will be added. For more information please refer to PostgreSQL Date/Time Formatting.

PostgreSQL

SELECT  to_char(current_date, 'HH'),
        to_char(TIMESTAMP WITH TIME ZONE '1997-12-22', 'DY-Y-MM'),
        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');
        
SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');

Snowflake

SELECT  to_char(current_date, 'HH') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(TIMESTAMP '1997-12-22', 'DD-MM-YYY') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'MS') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'US') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'SSSSS') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'IYYY') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DD-MM-YYY') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DY-Y-MM') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        to_char(current_date, 'DDDMMYY') /*** MSC-INFORMATION - MSCINF0041 - THE CURRENT TO_CHAR DATE FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;
        
SELECT
-- ** MSC-ERROR - MSC-PG0019 - THE USE OF INTERVAL WITHIN TO_CHAR IS NOT SUPPORTED BY SNOWFLAKE. **
--       to_char(interval '15h 2m 12s', 'HH24:MI:SS')
                                                   ;

And the following cases are fully supported in Snowflake.

PostgreSQL

SELECT to_char(timestamp '2023-01-20 17:31:12.66', 'HH12:MI:SS'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DY-YY-MM'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DYYYMM'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DDYYYYMM'),
        to_char(timestamp '2023-01-20 17:31:12.66','HH12:MI:SS');

Snowflake

SELECT to_char(timestamp '2023-01-20 17:31:12.66', 'HH12:MI:SS'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DY-YY-MM'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DYYYMM'),
        to_char(timestamp '2023-01-20 17:31:12.66', 'DDYYYYMM'),
        to_char(timestamp '2023-01-20 17:31:12.66','HH12:MI:SS');

Snowflake supports the following formats:

Format Element
Description

YYYY

Year (4 or more digits).

YY

Last 2 digits of year.

MM

Month number (01–12).

MON

Month name (3 chars in English, localized lengths vary).

DD

Day of month (01–31).

DY

Day name (3 chars in English, localized lengths vary).

HH24

Hour of day (01–12).

HH12

Hour of day (00–23).

FF1

Tenth of second (0–9).

FF2

Hundredth of second (00–99).

FF3

Millisecond (000–999).

FF4

Tenth of a millisecond (0000–9999).

FF5

Hundredth of a millisecond (00000–99999).

FF6

Microsecond (000000–999999).

Related EWIs

  1. MSCINF0041: THE CURRENT TO_CHAR DATE/TIME FORMAT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE.

  2. MSC-PG0012: THE CURRENT TO_CHAR NUMERIC FORMAT MAY FAIL OR HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE.

  3. MSC-PG0019: THE USE OF INTERVAL WITHIN TO_CHAR IS NOT SUPPORTED BY SNOWFLAKE.

PreviousData Type Formatting FunctionsNextTo Timestamp

Last updated

Was this helpful?