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

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

Snowflake

Query

SELECT
TO_CHAR(date '2012-12-23') /*** MSC-WARNING - MSCEWI2055 - 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') /*** MSC-WARNING - MSCEWI2055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
PUBLIC.DAY_NAME_LONG_UDF(date '2012-12-23', 'uppercase') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DAY_NAME_LONG_UDF' INSERTED. ***/ || TO_CHAR(date '2012-12-23', ' DD, ') || PUBLIC.MONTH_SHORT_UDF(date '2012-12-23', 'uppercase') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'MONTH_SHORT_UDF' INSERTED. ***/ || TO_CHAR(date '2012-12-23', ' YY') /*** MSC-WARNING - MSCEWI2055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/;

Result

TO_CHAR(Numeric) transformation

Teradata

Query

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

Result

Snowflake

Query

SELECT
TO_CHAR(1255.495) /*** MSC-WARNING - MSCEWI2055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/,
TO_CHAR(1255.495, '9.0EEEE') /*** MSC-WARNING - MSCEWI2055 - 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') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INSERT_CURRENCY_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/;

Result

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 MSCEWI2055.

Related EWIs

1. MSCEWI2055: Snowflake supported formats for TO_CHAR differ from Teradata and may fail or have different behavior.