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

Code

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

Result

 to_char | to_char | to_char 
---------+---------+---------
 -485    |    12   |   60

Snowflake

Code

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. ***/;

Result

 to_char | to_char | to_char 
---------+---------+---------
 -485    |    12   |   60

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

Code

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

Result

 to_char | to_char  | to_char | to_char | to_char | to_char | to_char |  to_char  | to_char
---------+----------+---------+---------+---------+---------+---------+-----------+----------
 12      | MON-7-12 | 000     | 000000  | 0       | 2023    | 18-07-023 | TUE-3-07 | 1990723
 
  to_char  
----------
 15:02:12

Snowflake

Code

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

Result

 to_char | to_char   | to_char | to_char | to_char | to_char |  to_char  | to_char  | to_char 
---------+-----------+---------+---------+---------+---------+-----------+----------+---------
 12      | 22-12-97Y | MS      | US      | 0000S   | I23Y    | 18-07-23Y | Tue-Y-07 | 18D0723

And the following cases are fully supported in Snowflake.

PostgreSQL

Code

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

Result

to_char   | to_char   | to_char | to_char  | to_char
----------+-----------+---------+----------+---------- 
05:31:12  | FRI-23-01 | FRI2301 | 20202301 | 05:31:12

Snowflake

Code

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

Result

to_char   | to_char   | to_char | to_char  | to_char
----------+-----------+---------+----------+---------- 
05:31:12  | Fri-23-01 | Fri2301 | 20202301 | 05:31:12

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.