MSCEWI2055

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Both Teradata and Snowflake offer the function TO_CHAR, which allows transforming multiple data types to varchar. However, they have some key differences:

• For datetime, Teradata offers multiple format elements to retrieve information of dates. For example Q, WW and D get the quarter number, the number of week and the week day, respectively. Snowflake also offers the option to retrieve this information, but in the form of functions like QUARTER, WEEK or DAYOFWEEK instead of format elements.

• For numeric types Teradata accepts nls parameters, these parameters allow to modify the default values of various currency format elements, as well as group and radix separators. Snowflake TO_CHAR does not support nls parameters.

Known issues

Although there are differences, SnowConvert supports and converts the TO_CHAR function. However, there are some format elements and specific cases that present functional differences and should be considered when using TO_CHAR.

Non-literal format and/or nls parameters strings

Snowconvert only processes and transforms TO_CHAR function calls that have literal format values, the nls parameters argument is considered only if it is also a literal value. Function calls with non-literal format and/or nls parameters are not guaranteed to work.

TO_CHAR(Datetime) format elements that are not supported or show a different behavior

AD, A.D., BC, B.C. (not supported):

These format elements are used to mark if a date is before or after the birth of Christ. In both Teradata and Snowflake the years part of a date ranges from 1 to 9999, meaning BC dates can`t be represented, using any of these format elements in Snowflake will result in the format element being directly copied to the resulting varchar.

A.M. and P.M. (formatting difference):

These format elements specify if a time in 12 hours format is ante meridiem or post meridiem, adding dots in between, these are mapped to AM and PM respectively which hold the same functionality but do not put dots in the result.

Teradata:

select to_char(time '22:45:30', 'HH:MI:SS A.M.'); -- returns '10:45:30 P.M.'

Snowflake:

SELECT TO_CHAR(time '22:45:30', 'HH12:MI:SS AM'); -- returns '10:45:30 PM'

DL and DS (different behavior):

Equivalent to using the formats 'FMDay, Month FMDD, YYYY' and 'FMMM/DD/YYYYFM' respectively, the difference comes from FM not being supported (see below).

Teradata:

 select to_char(date '2010-05-08', 'DS'); -- returns '5/8/2010'

Snowflake:

SELECT TO_CHAR(date '2010-05-08', 'MM/DD/YYYY'); -- returns '05/08/2010'

FF (different behavior):

In both Teradata and Snowflake not specifying a precision for this format element results in the precision of the input parameter being used (showing only the necessary number of precision digits). However, keep in mind that in Snowflake, datatypes with precision default to 9 unless it is explicitly specified, this can lead to inconsistencies in the results.

Teradata:

select to_char(time '17:30:15.45869', 'HH:MI:SSFF'); -- returns '05:30:15.45869'

Snowflake:

SELECT TO_CHAR(time '17:30:15.45869', 'HH12:MI:SS.FF'); -- returns '05:30:15.458690000'

FM (not supported):

Gets rid of zero values in the format elements that follow it, can be deactivated by adding another FM.

TZH and TZM (different behavior):

These format elements add the timezone hour and minutes respectively, Snowflake offers three timestamp variations that behave differently: the TIMESTAMP_TZ type works as expected returning its own timezone,TIMESTAMP_LTZ returns the timezone of the server and TIMESTAMP_NTZ can not hold timezone information so a 'Z' is returned. Unlike Teradata the TIME datatype can not hold timezone information either so it also returns 'Z'.

Teradata:

select to_char(timestamp '2018-09-13 10:30:26+05:30', 'TZH:TZM'); -- returns '+05:30'

Snowflake:

SELECT TO_CHAR(timestamp '2018-09-13 10:30:26+05:30', 'TZH:TZM'); -- returns 'Z'

SP (not supported):

Produces the spelled version of its preceding format element.

SYEAR and YEAR (not supported):

Produces the spelled version of the year part of the input.

TO_CHAR(Numeric) format elements that are not supported or show a different behavior

B (different behavior):

Replaces a numeric value of 0 with a blank, works as expected for integer values, but for decimal numbers in Snowflake it only replaces to blank the integer part instead of the whole number like it does in Teradata.

Teradata:

select to_char(0.596, 'B999.000'); -- returns '        '

Snowflake:

SELECT TO_CHAR(0.596, 'B999.000'); -- returns '.596'

TME (different behavior):

Represents the number in scientific notation using the smallest number of characters. In Snowflake the sign of the exponent is ignored if it is positive and the exponent is represented only with the necessary digits, Teradata on the other hand always includes the sign of the exponent and represents it with at least two digits.

Teradata:

select to_char(54896.236, 'TME'); -- returns '5.4896236E+04'

Snowflake:

SELECT TO_CHAR(54896.236, 'TME'); -- returns '5.4896236E4'

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') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'ROMAN_NUMERALS_MONTH_UDF' INSERTED. ***/ || TO_CHAR(date '2008-09-13', '/YYYY') /*** MSC-WARNING - MSCEWI2055 - 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') /*** MSC-WARNING - MSCEWI2055 - 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') /*** 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 ***/;

SELECT
TO_CHAR(45620) /*** MSC-WARNING - MSCEWI2055 - 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 a first parameter of type TIMESTAMP_TZ to avoid different behavior, also remember that the TIME type can not have timezone 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]