Snowflake supported formats for TO_CHAR differ from Teradata and may fail or have different behavior
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
DAYOFWEEKinstead 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.
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.
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.
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.
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.
select to_char(time '22:45:30', 'HH:MI:SS A.M.'); -- returns '10:45:30 P.M.'
SELECT TO_CHAR(time '22:45:30', 'HH12:MI:SS AM'); -- returns '10:45:30 PM'
Equivalent to using the formats
'FMDay, Month FMDD, YYYY'and
'FMMM/DD/YYYYFM'respectively, the difference comes from FM not being supported (see below).
select to_char(date '2010-05-08', 'DS'); -- returns '5/8/2010'
SELECT TO_CHAR(date '2010-05-08', 'MM/DD/YYYY'); -- returns '05/08/2010'
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.
select to_char(time '17:30:15.45869', 'HH:MI:SSFF'); -- returns '05:30:15.45869'
SELECT TO_CHAR(time '17:30:15.45869', 'HH12:MI:SS.FF'); -- returns '05:30:15.458690000'
Gets rid of zero values in the format elements that follow it, can be deactivated by adding another FM.
These format elements add the timezone hour and minutes respectively, Snowflake offers three timestamp variations that behave differently: the
TIMESTAMP_TZtype works as expected returning its own timezone,
TIMESTAMP_LTZreturns the timezone of the server and
TIMESTAMP_NTZcan not hold timezone information so a 'Z' is returned. Unlike Teradata the
TIMEdatatype can not hold timezone information either so it also returns 'Z'.
select to_char(timestamp '2018-09-13 10:30:26+05:30', 'TZH:TZM'); -- returns '+05:30'
SELECT TO_CHAR(timestamp '2018-09-13 10:30:26+05:30', 'TZH:TZM'); -- returns 'Z'
Produces the spelled version of its preceding format element.
Produces the spelled version of the year part of the input.
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.
select to_char(0.596, 'B999.000'); -- returns ' '
SELECT TO_CHAR(0.596, 'B999.000'); -- returns '.596'
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.
select to_char(54896.236, 'TME'); -- returns '5.4896236E+04'
SELECT TO_CHAR(54896.236, 'TME'); -- returns '5.4896236E4'
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
DAYOFWEEKfunction, the results of this function depend on the
WEEK_STARTsession parameter, by default Teradata considers Sunday as the first day of the week, while in Snowflake it is Monday.
- WW: Mapped to
WEEKfunction, this function depends on the session parameter
WEEK_OF_YEAR_POLICYwhich 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.
The single parameter version of
TO_CHAR(Datetime)makes use of the default formats specified in the session parameters
TIME_OUTPUT_FORMAT. To avoid differences in behavior please set them to the same values used in Teradata.
TO_CHAR(Numeric)Snowflake generates the varchar representation using either the
TMEformats to get a compact representation of the number, Teradata also generates compact representations of the numbers so no action is required.
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''');
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 ***/;
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 ***/;
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 ***/;
TO_CHAR(45620) /*** MSC-WARNING - MSCEWI2055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/;
- 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_TZto avoid different behavior, also remember that the
TIMEtype can not have timezone information in Snowflake.
- Set the necessary session parameters with the default values from Teradata to avoid different behavior.
- For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.