TO_CHAR

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

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

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

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.