TO_CHAR

Translation specification for transforming the TO_CHAR function into an equivalent function concatenation in Snowflake

Description

The TO_CHAR function casts a DateTime or numeric value to a string. For more information check TO_CHAR(Numeric) and TO_CHAR(DateTime).

-- Numeric version
[TD_SYSFNLIB.]TO_CHAR(numeric_expr [, format_arg [, nls_param]])

-- DateTime version
[TD_SYSFNLIB.]TO_CHAR(dateTime_expr [, format_arg])

Both Snowflake and Teradata have their own version of the TO_CHAR function, however, Teradata supports plenty of formats that are not natively supported by Snowflake. To support these format elements SnowConvert uses Snowflake built-in functions and custom UDFs to generate a concatenation expression that produces the same string as the original TO_CHAR function in Teradata.

Sample Source Patterns

TO_CHAR(DateTime) transformation

Teradata

Query

IN -> Teradata_01.sql

SELECT 
TO_CHAR(date '2012-12-23'),
TO_CHAR(date '2012-12-23', 'DS'),
TO_CHAR(date '2012-12-23', 'DAY DD, MON YY');

Result

COLUMN1    | COLUMN2    | COLUMN3           |
-----------+------------+-------------------+
2012/12/23 | 12/23/2012 | SUNDAY 23, DEC 12 |

Snowflake

Query

OUT -> Teradata_01.sql

SELECT
TO_CHAR(date '2012-12-23') /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
TO_CHAR(date '2012-12-23', 'MM/DD/YYYY') /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
PUBLIC.DAYNAME_LONG_UDF(date '2012-12-23', 'uppercase') || TO_CHAR(date '2012-12-23', ' DD, ') || PUBLIC.MONTH_SHORT_UDF(date '2012-12-23', 'uppercase') || TO_CHAR(date '2012-12-23', ' YY') /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/;

Result

COLUMN1    | COLUMN2    | COLUMN3           |
-----------+------------+-------------------+
2012/12/23 | 12/23/2012 | SUNDAY 23, DEC 12 |

TO_CHAR(Numeric) transformation

Teradata

Query

IN -> Teradata_02.sql

SELECT
TO_CHAR(1255.495),
TO_CHAR(1255.495, '9.9EEEE'),
TO_CHAR(1255.495, 'SC9999.9999', 'nls_iso_currency = ''EUR''');

Result

COLUMN1  | COLUMN2 | COLUMN3       |
---------+---------+---------------+
1255.495 | 1.3E+03 | +EUR1255.4950 |

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
TO_CHAR(1255.495) /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
TO_CHAR(1255.495, '9.0EEEE') /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
PUBLIC.INSERT_CURRENCY_UDF(TO_CHAR(1255.495, 'S9999.0000'), 2, 'EUR') /*** SSC-FDM-TD0029 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/;

Result

COLUMN1  | COLUMN2 | COLUMN3       |
---------+---------+---------------+
1255.495 | 1.3E+03 | +EUR1255.4950 |

Known Issues

1. Formats with different or unsupported behaviors

Teradata offers an extensive list of format elements that may show different behavior in Snowflake after the transformation of the TO_CHAR function. For the list of elements with different or unsupported behaviors check SSC-EWI-TD0029.

Related EWIs

1. SSC-FDM-TD0029: Snowflake supported formats for TO_CHAR differ from Teradata and may fail or have different behavior.