SSC-EWI-TD0055

Snowflake supported formats for TO_CHAR differ from Teradata and may fail or have different behavior

This EWI is deprecated, please refer to SSC-FDM-TD0029 documentation

Format elements that depend on session parameters

Some Teradata format elements are mapped to Snowflake functions that depend on the value of session parameters. To avoid functional differences in the results you should set these session parameters to the same values they have in Teradata. Identified format elements that are mapped to this kind of functions are:

• D: Mapped to DAYOFWEEK function, the results of this function depend on the WEEK_START session parameter, by default Teradata considers Sunday as the first day of the week, while in Snowflake it is Monday.

• WW: Mapped to WEEK function, this function depends on the session parameter WEEK_OF_YEAR_POLICY which by default is set to use the ISO standard (the first week of year is the first to contain at least four days of January) but in Teradata is set to consider January first as the start of the first week.

To modify session parameters, use ALTER SESSION SET parameter_name = value. for more information about session parameters visit this page.

Single parameter version of TO_CHAR

The single parameter version of TO_CHAR(Datetime) makes use of the default formats specified in the session parameters TIMESTAMP_LTZ_OUTPUT_FORMAT, TIMESTAMP_NTZ_OUTPUT_FORMAT, TIMESTAMP_TZ_OUTPUT_FORMAT and TIME_OUTPUT_FORMAT. To avoid differences in behavior please set them to the same values used in Teradata.

For TO_CHAR(Numeric) Snowflake generates the varchar representation using either the TM9 or TME formats to get a compact representation of the number, Teradata also generates compact representations of the numbers so no action is required.

Example Code

Input Code:

select to_char(date '2008-09-13', 'DD/RM/YYYY');

select to_char(date '2010-10-20', 'DS');

select to_char(1255.495, 'SC9999.9999', 'nls_iso_currency = ''EUR''');

select to_char(45620);

Output Code:

SELECT
TO_CHAR(date '2008-09-13', 'DD/') || PUBLIC.ROMAN_NUMERALS_MONTH_UDF(date '2008-09-13') || TO_CHAR(date '2008-09-13', '/YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

SELECT
TO_CHAR(date '2010-10-20', 'MM/DD/YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

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

SELECT
TO_CHAR(45620) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

Recommendations

• When using FF either try to use DateTime types with the same precision that you use in Teradata or add a precision to the format element to avoid the different behavior.

• When using timezone-related format elements, use the first parameter of type TIMESTAMP_TZ to avoid different behavior. Also remember that the TIME type cannot have time zone information in Snowflake.

• Set the necessary session parameters with the default values from Teradata to avoid different behavior.

• If you need more support, you can email us at [e-mail]