Built-in Functions

This page provides a description of the translation for the built-in functions in Teradata to Snowflake

This page only lists the functions that are already transformed by SnowConvert, if a function from the Teradata documentation is not listed there then it should be taken as unsupported.

Some Teradata functions do not have a direct equivalent in Snowflake so they are transformed into a functional equivalent UDF, these can be easily spotted by the _UDF postfix in the name of the function. For more information on the UDFs SnowConvert uses check this git repository.

Aggregate Functions

Teradata	Snowflake	Note
AVG	AVG
CORR	CORR
COUNT	COUNT
COVAR_POP	COVAR_POP
COVAR_SAMP	COVAR_SAMP
GROUPING	GROUPING
KURTOSIS	KURTOSIS
MAXIMUM MAX	MAX
MINIMUM MIN	MIN
PIVOT	PIVOT	Check PIVOT.
REGR_AVGX	REGR_AVGX
REGR_AVGY	REGR_AVGY
REGR_COUNT	REGR_COUNT
REGR_INTERCEPT	REGR_INTERCEPT
REGR_R2	REGR_R2
REGR_SLOPE	REGR_SLOPE
REGR_SXX	REGR_SXX
REGR_SXY	REGR_SXY
REGR_SYY	REGR_SYY
SKEW	SKEW
STDDEV_POP	STDDEV_POP
STDDEV_SAMP	STDDEV_SAMP
SUM	SUM
UNPIVOT	UNPIVOT	Unpivot with multiple functions not supported in Snowflake
VAR_POP	VAR_POP
VAR_SAMP	VAR_SAMP

See Aggregate functions​

Arithmetic, Trigonometric, Hyperbolic Operators/Functions

Teradata	Snowflake	Note
ABS	ABS
CEILING	CEIL
DEGREES	DEGREES
EXP	EXP
FLOOR	FLOOR
HYPERBOLIC ACOSH ASINH ATANH COSH SINH TANH	HYPERBOLIC ACOSH ASINH ATANH COSH SINH TANH
LOG	LOG
LN	LN
MOD	MOD
NULLIFZERO(param)	CASE WHEN param=0 THEN null ELSE param END
POWER	POWER
RANDOM	RANDOM
RADIANS	RADIANS
ROUND	ROUND
SIGN	SIGN
SQRT	SQRT
TRUNC	TRUNC_UDF
TRIGONOMETRIC ACOS ASIN ATAN ATAN2 COS SIN TAN	TRIGONOMETRIC ACOS ASIN ATAN ATAN2 COS SIN TAN
ZEROIFNULL	ZEROIFNULL

See Arithmetic, Trigonometric, Hyperbolic Operators/Functions​

Attribute Functions

Teradata	Snowflake	Note
BIT_LENGTH	BIT_LENGTH
BYTE BYTES	LENGTH
CHAR CHARS CHARACTERS	LEN
CHAR_LENGTH CHARACTER_LENGTH	LEN
MCHARACTERS	LENGTH
OCTECT_LENGTH	OCTECT_LENGTH

See Attribute functions

Bit/Byte Manipulation Functions

Teradata	Snowflake	Note
BITAND	BITAND
BITNOT	BITNOT
BITOR	BITOR
BITXOR	BITXOR
GETBIT	GETBIT

See Bit/Byte functions

Built-In (System Functions)

Teradata	Snowflake	Note
ACCOUNT	CURRENT_ACCOUNT
CURRENT_DATE CURDATE	CURRENT_DATE
CURRENT_ROLE	CURRENT_ROLE
CURRENT_TIME CURTIME	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP
DATABASE	CURRENT_DATABASE
DATE	CURRENT_DATE
NOW	CURRENT_TIMESTAMP
PROFILE	CURRENT_ROLE	Check MSCEWI2068 for more details on this transformation
SESSION	CURRENT_SESSION
TIME	CURRENT_TIME
USER	CURRENT_USER

See Built-In Functions

Business Calendars

Teradata	Snowflake	Note
DAYNUMBER_OF_MONTH(DatetimeValue, 'COMPATIBLE')	DAYOFMONTH
DAYNUMBER_OF_MONTH(DatetimeValue, 'ISO')	DAYNUMBER_OF_MONTH_ISO_UDF
DAYNUMBER_OF_MONTH(DatetimeValue, 'TERADATA')	DAYOFMONTH
DAYNUMBER_OF_WEEK(DatetimeValue, 'ISO')	DAYOFWEEKISO
DAYNUMBER_OF_WEEK(DatetimeValue, 'COMPATIBLE')	DAY_OF_WEEK_COMPATIBLE_UDF
DAYNUMBER_OF_WEEK(DatetimeValue, 'TERADATA') DAYNUMBER_OF_WEEK(DatetimeValue)	TD_DAY_OF_WEEK_UDF
DAYNUMBER_OF_YEAR(DatetimeValue, 'ISO')	PUBLIC.DAY_OF_YEAR_ISO_UDF
DAYNUMBER_OF_YEAR(DatetimeValue)	DAYOFYEAR
QUARTERNUMBER_OF_YEAR	QUARTER
TD_SUNDAY(DateTimeValue)	PREVIOUS_DAY(DateTimeValue, 'Sunday')
WEEKNUMBER_OF_MONTH	WEEKNUMBER_OF_MONTH_UDF
WEEKNUMBER_OF_QUARTER(dateTimeValue)	WEEKNUMBER_OF_QUARTER_UDF
WEEKNUMBER_OF_QUARTER(dateTimeValue, 'ISO')	WEEKNUMBER_OF_QUARTER_ISO_UDF
WEEKNUMBER_OF_QUARTER(dateTimeValue, 'COMPATIBLE')	WEEKNUMBER_OF_QUARTER_COMPATIBLE_UDF
WEEKNUMBER_OF_YEAR(DateTimeValue, 'ISO')	WEEKISO
YEARNUMBER_OF_CALENDAR(DATETIMEVALUE, 'COMPATIBLE')	YEAR
YEARNUMBER_OF_CALENDAR(DATETIMEVALUE, 'ISO')	YEAROFWEEKISO

See Business Calendars

Calendar Functions

Teradata	Snowflake	Note
DAYNUMBER_OF_WEEK(DatetimeValue)	TD_DAY_OF_WEEK_UDF
DAYNUMBER_OF_WEEK(DatetimeValue, 'COMPATIBLE')	DAY_OF_WEEK_COMPATIBLE_UDF
QuarterNumber_Of_Year(DatetimeValue, 'ISO')	QUARTER_OF_YEAR_ISO_UDF(DatetimeValue)
TD_DAY_OF_CALENDAR	TD_DAY_OF_CALENDAR_UDF
TD_DAY_OF_MONTH DAYOFMONTH	DAYOFMONTH
TD_DAY_OF_WEEK DAYOFWEEK	TD_DAY_OF_WEEK_UDF
TD_DAY_OF_YEAR	DAYOFYEAR
TD_MONTH_OF_CALENDAR(DateTimeValue) MONTH_CALENDAR(DateTimeValue)	TD_MONTH_OF_CALENDAR_UDF(DateTimeValue)
TD_WEEK_OF_CALENDAR(DateTimeValue) WEEK_OF_CALENDAR(DateTimeValue)	TD_WEEK_OF_CALENDAR_UDF(DateTimeValue)
TD_WEEK_OF_YEAR	WEEK_OF_YEAR_UDF
TD_YEAR_BEGIN(DateTimeValue)	YEAR_BEGIN_UDF(DateTimeValue)
TD_YEAR_BEGIN(DateTimeValue, 'ISO')	YEAR_BEGIN_ISO_UDF(DateTimeValue)
TD_YEAR_END(DateTimeValue)	YEAR_END_UDF(DateTimeValue)
TD_YEAR_END(DateTimeValue, 'ISO')	YEAR_END_ISO_UDF(DateTimeValue)
WEEKNUMBER_OF_MONTH(DateTimeValue)	WEEKNUMBER_OF_MONTH_UDF(DateTimeValue)
WEEKNUMBER_OF_QUARTER(DateTimeValue)	WEEKNUMBER_OF_QUARTER_UDF(DateTimeValue)
WEEKNUMBER_OF_QUARTER(DateTimeValue, 'ISO')	WEEKNUMBER_OF_QUARTER_ISO_UDF(DateTimeValue)
WEEKNUMBER_OF_QUARTER(DateTimeValue, 'COMPATIBLE')	WEEKNUMBER_OF_QUARTER_COMPATIBLE_UDF(DateTimeValue)
WEEKNUMBER_OF_YEAR(DateTimeValue)	WEEK_OF_YEAR_UDF(DateTimeValue)
WEEKNUMBER_OF_YEAR(DateTimeValue, 'COMPATIBLE')	WEEK_OF_YEAR_COMPATIBLE_UDF(DateTimeValue)

See Calendar Functions

Case Functions

Teradata	Snowflake	Note
COALESCE	COALESCE	Check Coalesce.
NULLIF	NULLIF

See case functions

Comparison Functions

Teradata	Snowflake	Note
DECODE	DECODE
GREATEST	GREATEST
LEAST	LEAST

See comparison functions

Data type conversions

Teradata	Snowflake	Note
CAST	CAST
CAST(DatetimeValue AS INT)	DATE_TO_INT_UDF
CAST (VarcharValue AS INTERVAL)	INTERVAL_UDF	Check Cast to INTERVAL datatype
TRYCAST	TRY_CAST
FROM_BYTES	TO_NUMBER TO_BINARY	FROM_BYTES with ASCII parameter not supported in Snowflake.

See Data Type Conversions

Data Type Conversion Functions

Teradata	Snowflake	Note
TO_BYTES(Input, 'Base10')	INT2HEX_UDF(Input)
TO_NUMBER	TO_NUMBER
TO_CHAR	TO_CHAR or equivalent expression	Check TO_CHAR.
TO_DATE	TO_DATE
TO_DATE(input, 'YYYYDDD')	JULIAN_TO_DATE_UDF

See Data Type Conversion Functions

DateTime and Interval functions

Teradata	Snowflake	Note
ADD_MONTHS	ADD_MONTHS
EXTRACT	EXTRACT
LAST_DAY	LAST_DAY
MONTH	MONTH
MONTHS_BETWEEN	MONTHS_BETWEEN_UDF
NEXT_DAY	NEXT_DAY
OADD_MONTHS	ADD_MONTHS
ROUND(Numeric)	ROUND
ROUND(Date)	ROUND_DATE_UDF
TRUNC(Date)	TRUNC_UDF
YEAR	YEAR

See DateTime and Interval Functions and Expressions

Hash functions

Teradata	Snowflake	Note
HASH_MD5	MD5
HASHAMP HASHBACKAM HASHBUCKET HASHROW	Not supported	Check notes on the architecture differences between Teradata and Snowflake

See Hash functions

JSON functions

Teradata	Snowflake	Note
NEW JSON	TO_JSON(PARSE_JSON())	Check NEW JSON
JSON_CHECK	CHECK_JSON	Check JSON_CHECK
JSON_TABLE	Equivalent query	Check JSON_TABLE
JSONExtract JSONExtractValue JSONExtractLargeValue	JSON_EXTRACT_UDF	Check JSON_EXTRACT

See JSON documentation

Null-Handling functions

Teradata	Snowflake	Note
NVL	NVL
NVL2	NVL2

See Null-Handling functions

Ordered Analytical/Window Aggregate functions

Teradata	Snowflake	Note
CSUM(col1, col2)	SUM(col_1) OVER (PARTITION BY null ORDER BY col_2 ROWS UNBOUNDED PRECEDING)
CUME_DIST	CUME_DIST
DENSE_RANK	DENSE_RANK
FIRST_VALUE	FIRST_VALUE
LAG	LAG
LAST_VALUE	LAST_VALUE
LEAD	LEAD
MAVG(csales, 2, cdate, csales)	AVG(csales) OVER ( ORDER BY cdate, csales ROWS 1 PRECEDING)
MEDIAN	MEDIAN
MSUM(csales, 2, cdate, csales)	SUM(csales) OVER(ORDER BY cdate, csales ROWS 1 PRECEDING)
PERCENT_RANK	PERCENT_RANK
PERCENTILE_CONT	PERCENTILE_CONT
PERCENTILE_DISC	PERCENTILE_DISC
QUANTILE	QUANTILE
RANK	RANK
ROW_NUMBER	ROW_NUMBER

See Window functions

Period functions and operators

Teradata	Snowflake	Note
BEGIN	PERIOD_BEGIN_UDF
END	PERIOD_END_UDF
INTERVAL	TIMESTAMPDIFF
LAST	PERIOD_LAST_UDF
LDIFF	PERIOD_LDIFF_UDF
OVERLAPS	PUBLIC.PERIOD_OVERLAPS_UDF
PERIOD	PERIOD_UDF
PERIOD(datetimeValue, UNTIL_CHANGED) PERIOD(datetimeValue, UNTIL_CLOSED)	PERIOD_UDF(datetimeValue, '9999-12-31 23:59:59.999999')	See notes about ending bound constants
RDIFF	PERIOD_RDIFF_UDF

See Period Functions and Operators

Query band functions

Teradata	Snowflake	Note
GETQUERYBANDVALUE	GETQUERYBANDVALUE_UDF	Check GETQUERYBANDVALUE

See Query band functions

Regex functions

Teradata	Snowflake	Note
REGEXP_INSTR	REGEXP_INSTR	Check Regex functions
REGEXP_REPLACE	REGEXP_REPLACE	Check Regex functions
REGEXP_SIMILAR	REGEXP_LIKE	Check Regex functions
REGEXP_SUBSTR	REGEXP_SUBSTR	Check Regex functions

See Regex functions

String operators and functions

Teradata	Snowflake	Note
ASCII	ASCII
CHAR2HEXINT	CHAR2HEXINT_UDF
CHR	CHR/CHAR
CHAR_LENGTH	LEN
CONCAT	CONCAT
EDITDISTANCE	EDITDISTANCE
INDEX	CHARINDEX	Check notes about implicit conversion
INITCAP	INITCAP
INSTR	REGEXP_INSTR
INSTR(StringValue, StringValue ,NumericNegativeValue, NumericValue)	INSTR_UDF(StringValue, StringValue ,NumericNegativeValue, NumericValue)
LEFT	LEFT
LENGTH	LENGTH
LOWER	LOWER
LPAD	LPAD
LTRIM	LTRIM
OREPLACE	REPLACE
OTRANSLATE	TRANSLATE
POSITION	POSITION	Check notes about implicit conversion
REVERSE	REVERSE
RIGHT	RIGHT
RPAD	RPAD
RTRIM	RTRIM
SOUNDEX	SOUNDEX_P123
STRTOK	STRTOK
STRTOK_SPLIT_TO_TABLE	STRTOK_SPLIT_TO_TABLE	Check Strtok_split_to_table
SUBSTRING	SUBSTR/SUBSTR_UDF	Check Substring
TRANSLATE_CHK	TRANSLATE_CHK_UDF
TRIM(LEADING '0' FROM aTABLE)	LTRIM(aTABLE, '0')
TRIM(TRAILING '0' FROM aTABLE)	RTRIM(aTABLE, '0')
TRIM(BOTH '0' FROM aTABLE)	TRIM(aTABLE, '0')
TRIM(CAST(numericValue AS FORMAT '999'))	LPAD(numericValue, 3, 0)
UPPER	UPPER

See String operators and functions​​​

St_Point functions

Teradata	Snowflake	Note
ST_SPHERICALDISTANCE	HAVERSINE ST_DISTANCE

See St_Point functions

Table operators

Teradata	Snowflake	Note
TD_UNPIVOT	Equivalent query	Check Td_unpivot

See Table Operators

XML functions

Teradata	Snowflake	Note
XMLAGG	LISTAGG	Check Xmlagg
XMLQUERY	Not Supported

See XML functions

Extensibility UDFs

This section contains UDFs and other extensibility functions that are not offered as system built-in functions by Teradata but are transformed by SnowConvert

Teradata	Snowflake	Note
CHKNUM	CHKNUM_UDF	Check this UDF download page

Notes

Architecture differences between Teradata and Snowflake

Teradata has a shared-nothing architecture with Access Module Processors (AMP) where each AMP manages their own share of disk storage and is accessed through hashing when doing queries. To take advantage of parallelism the stored information should be evenly distributed among AMPs and to do this Teradata offers a group of hash-related functions that can be used to determine how good the actual primary indexes are.

On the other hand, Snowflake architecture is different, and it manages how the data is stored on its own, meaning users do not need to worry about optimizing their data distribution.

Ending bound constants (UNTIL_CHANGED and UNTIL_CLOSED)

Both UNTIL_CHANGED and UNTIL_CLOSED are Teradata constants that represent an undefined ending bound for periods. Internally, these constants are represented as the maximum value a timestamp can have i.e '9999-12-31 23:59:59.999999'. During the migration of the PERIOD function, the ending bound is checked if present to determine if it is one of these constants and to replace it with varchar of value '9999-12-31 23:59:59.999999' in case it is, Snowflake then casts the varchar to date or timestamp depending on the type of the beginning bound when calling PERIOD___UDF.

Implicit conversion

Some Teradata string functions like INDEX or POSITION accept non-string data types and implicitly convert them to string, this can cause inconsistencies in the results of those functions between Teradata and Snowflake. For example, the following Teradata code:

IN -> Teradata_01.sql

SELECT INDEX(35, '5');

Returns 4, while the CHARINDEX equivalent in Snowflake:

SELECT CHARINDEX('5', 35);

Returns 2, this happens because Teradata has its own default formats which are used during implicit conversion. In the above example, Teradata interprets the numeric constant 35 as BYTEINT and uses BYTEINT default format'-999' for the implicit conversion to string, causing the converted value to be ' 35'. On the other hand, Snowflake uses its own default formats, creating inconsistencies in the result.

To solve this, the following changes are done to those function parameters:

• If the parameter does not have a cast with format, then a snowflakeTO_VARCHARfunction with the default Teradata format equivalent in Snowflake is added instead.

• If the parameter does have a cast with format, then the format is converted to its Snowflake equivalent and theTO_VARCHARfunction is added.

  • As a side note, Teradata ignores the sign of a number if it is not explicitly put inside a format, while Snowflake always adds spaces to insert the sign even when not specified, for those cases a check is done to see if the sign was specified and to remove it from the Snowflake string in case it was not.

After these changes, the resulting code would be:

OUT -> Teradata_01.sql

SELECT CHARINDEX( '5', TO_VARCHAR(35, 'MI999'));

Which returns 4, the same as the Teradata code.

Known Issues

No issues were found.

Related EWIs

No related EWIs.