SnowConvert: Function References for Teradata¶

Definition¶

UDF that reproduces teradata QUARTERNUMBER_OF_YEAR_UDF(date, ‘ISO’) functionality.

PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The to get from the number of the quarter.

Returns¶

An integer that represents the quarter that the date belongs to.

Usage example¶

Input:

SELECT PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(DATE '2022-01-01'),
PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(DATE '2025-12-31');

Output:

4, 1

Definition¶

Returns the day of the year a timestamp that belongs to. It has the same behavior as the DAYNUMBER_OF_YEAR(DATE, ‘ISO’).

PUBLIC.DAYNUMBER_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

A date to get the day number of the year.

Returns¶

An integer between 1 and 371.

Example¶

Input:

SELECT DAYNUMBER_OF_YEAR(CURRENT_DATE,'ISO');

Output:

SELECT
PUBLIC.DAYNUMBER_OF_YEAR_UDF(CURRENT_DATE());

Definition¶

Extracts a substring from a named string based on position.

PUBLIC.SUBSTR_UDF(BASE_EXPRESSION STRING, START_POSITION FLOAT)

Parameters¶

BASE_EXPRESSION STRING

The expression from which the substring is to be extracted.

START_POSITION FLOAT

The starting position of the substring to extract from.

Returns¶

The required substring.

Migration example¶

Input:

SELECT SUBSTRING('Hello World!' FROM -2);

Output:

SELECT
PUBLIC.SUBSTR_UDF('Hello World!', -2);

Definition¶

Check if a string value represents a numeric value.

PUBLIC.CHKNUM_UDF(NUM STRING);

Parameters¶

NUM STRING

The value of the string to be checked.

Returns¶

Returns 1 if the parameter is a valid number. Otherwise, returns 0.

Example¶

Input:

SELECT CHKNUM('1032');

Output:

SELECT
PUBLIC.CHKNUM_UDF('1032');

Definition¶

UDF that reproduces the TD_YEAR_END(DATE) or TD_YEAR_END(DATE, ‘COMPATIBLE’) functionality from Teradata.

PUBLIC.TD_YEAR_END_UDF(INPUT date)

Parameters¶

INPUT DATE

The date to get the last day of the year.

Returns¶

The last day of the year.

Usage example¶

Input:

SELECT  PUBLIC.TD_YEAR_END_UDF(DATE '2022-01-01'),
PUBLIC.TD_YEAR_END_UDF(DATE '2022-04-12');

Output:

2022-12-31, 2022-12-31

Definition¶

User-defined function (UDF) that reproduces the OVERLAPS OPERATOR to compare two or more period expressions and indicate if all expressions overlap with each other.

PERIOD_OVERLAPS_UDF(PERIODS ARRAY)

Parameters¶

PERIODS ARRAY

All the period expressions to be compared.

Returns¶

TRUE if all period expressions overlap, otherwise FALSE.

Migration example¶

Input:

SELECT
	PERIOD(DATE '2009-01-01', DATE '2010-09-24')
	OVERLAPS
	PERIOD(DATE '2009-02-01', DATE '2009-06-24');

Output:

SELECT
	PUBLIC.PERIOD_OVERLAPS_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

Definition¶

Defines the week number from the start of the quarter to the specified date.

PUBLIC.WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the week number of the quarter.

Returns¶

An integer that represents the number of the week in the quarter.

Usage example¶

Input:

SELECT WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(DATE '2022-05-01', 'COMPATIBLE'),
WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(DATE '2022-07-06', 'COMPATIBLE')

Output:

5, 1

Definition¶

Determines the month of a given date represented in Roman numerals.

PUBLIC.ROMAN_NUMERALS_MONTH_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the month from.

Returns¶

A varchar which is the month of a given date.

Usage example¶

Input:

SELECT PUBLIC.ROMAN_NUMERALS_MONTH_UDF(DATE '2021-10-26');

Output:

'X'

Definition¶

User-defined function (UDF) that reproduces the functionality of TD_YEAR_BEGIN or TD_YEAR_BEGIN( DATE, ‘COMPATIBLE’).

PUBLIC.TD_YEAR_BEGIN_UDF(INPUT DATE)

Parameters¶

INPUT DATE

The date to get the first day of the year.

Returns¶

The first day of the year.

Usage example¶

Input:

SELECT TD_YEAR_BEGIN(DATE '2022-01-01', 'COMPATIBLE'),
TD_YEAR_BEGIN(DATE '2022-04-12');

Output:

2022-01-01, 2022-01-01

Definition¶

Returns the complete name of a month either in uppercase, lowercase, or with only the first letter in uppercase.

PUBLIC.FULL_MONTH_NAME_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the name of the month.

RESULTCASE VARCHAR

The case the result to be represented, accepted values are ‘uppercase’, ‘lowercase’, and ‘capitalized’

Returns¶

Returns a varchar with the complete name of a month

Usage example¶

Input:

SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'firstOnly');

Output:

OCTOBER
october
October

Definition¶

Converts the input numerical value in base 10 to its hexadecimal equivalent.

TO_BYTES_HEX_UDF(INPUT FLOAT)

Parameters¶

INPUT FLOAT

The numerical value to be converted to hexadecimal.

Returns¶

A string which is the hexadecimal equivalence.

Usage example¶

Input:

SELECT TO_BYTES_HEX_UDF('448');

Output:

01c0

Definition¶

User-defined function (UDF) that reproduces the P_INTERSECT operator to compare two or more period expressions and return the overlapping portion of the period expressions.

More information about the source function here.

PERIOD_INTERSECT_UDF(PERIODS ARRAY)

Parameters¶

PERIODS ARRAY

All the period expressions to be compared.

Returns¶

The overlapped portion of the period expressions.

Migration example¶

Input:

SELECT
	PERIOD(DATE '2009-01-01', DATE '2010-09-24')
	P_INTERSECT
	PERIOD(DATE '2009-02-01', DATE '2009-06-24');

Output:

SELECT
	PUBLIC.PERIOD_INTERSECT_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

Definition¶

Transforms interval to seconds.

PUBLIC.INTERVAL_TO_SECONDS_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR())

Parameters¶

INPUT_PART VARCHAR

The interval to be converted to seconds.

INPUT_VALUE VARCHAR

The type to be converted. E.g. ‘DAY’, ‘DAY TO HOUR’, etc.

Returns¶

A decimal with the processed interval as seconds.

Definition¶

Allows the addition of two different timestamps.

PUBLIC.TIMESTAMP_ADD_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)

Parameters¶

FIRST_DATE TIMESTAMP_LTZ

The first date of the addition.

SECOND_DATE TIMESTAMP_LTZ

The second date of the addition.

Returns¶

A timestamp resulting from the addition of the parameters dates.

Definition¶

User-defined function (UDF) for the INTERVAL_MULTIPLY function.

PUBLIC.INTERVAL_MULTIPLY_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR(), INPUT_MULT INTEGER)

Parameters¶

INPUT_PART VARCHAR

The value of the multiplication e.g. ‘YEAR TO MONTH’.

INPUT_VALUE VARCHAR

The interval that is multiplicated to.

INPUT_MULT INTEGER

The numeric expression to be multiplied.

Returns¶

The result of an expression is a multiplication between an interval and a numeric expression.

Migration example¶

Input:

SELECT INTERVAL '6-10' YEAR TO MONTH * 8;

Output:

SELECT
PUBLIC.INTERVAL_MULTIPLY_UDF('YEAR TO MONTH', '6-10', 8);

Definition¶

User-defined function (UDF) that reproduces the equivalent functionality of Teradata’s TD_DAY_OF_WEEK. More information about the source function is here.

PUBLIC.TD_DAY_OF_WEEK_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

Date to get the day of the week from.

Returns¶

An integer between 1 and 7 where 1 is Sunday, 2 is Monday, 3 is Tuesday, …, 7 is Saturday.

Migration example¶

Input:

SELECT td_day_of_week(DATE '2022-03-02');

Output:

SELECT
PUBLIC.TD_DAY_OF_WEEK_UDF(DATE '2022-03-02');

Definition¶

Determines the iso year from a given date truncated to the specified number of digits.

PUBLIC.ISO_YEAR_PART_UDF(INPUT TIMESTAMP_TZ, DIGITS INTEGER)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the iso year from.

DIGITS INTEGER

The amount of digits wanted in the result.

Returns¶

Returns a varchar which is the iso year from a given date.

Usage example¶

Input:

SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 3);
SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 2);
SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 1);

Output:

'021'
'21'
'1'

Definition¶

Calculates the difference between two dates depending on the time parameter.

PUBLIC.DIFF_TIME_PERIOD_UDF(TIME STRING, PERIOD VARCHAR(50))

Parameters¶

TIME STRING

The time to be used in the anchor.

PERIOD VARCHAR

The period column that is used in the expand on.

Returns¶

A number that represents the difference between two dates.

Usage example¶

Input:

SELECT DIFF_TIME_PERIOD_UDF('SECONDS','2022-11-26 10:15:20.000*2022-11-26 10:15:25.000');

Output:

5

Definition¶

Determines the week number from the start of the quarter to the specified date. It has the same behavior as WEEKNUMBER_OF_QUARTER(DATE, 'ISO') function with ISO calendar from Teradata.

PUBLIC.WEEK_NUMBER_OF_QUARTER_ISO_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the week number of the quarter.

Returns¶

An integer that represents the number of the week in the quarter.

Usage example¶

Input:

SELECT WEEKNUMBER_OF_QUARTER(DATE '2022-05-01', 'ISO'),
WEEKNUMBER_OF_QUARTER(DATE '2022-07-06', 'ISO')

Output:

SELECT
PUBLIC.SUBSTR_UDF('Hello World!', -2);

Definition¶

Equivalent to the NVP Teradata’s function.

NVP_UDF(INSTRING VARCHAR, NAME_TO_SEARCH VARCHAR, NAME_DELIMITERS VARCHAR, VALUE_DELIMITERS VARCHAR, OCCURRENCE FLOAT)

Parameters¶

INSTRING VARCHAR

The name-value pairs.

NAME_TO_SEARCH VARCHAR

The name to search in the NVP function.

NAME_DELIMITERS VARCHAR

The delimiter to separate the name-value pairs.

VALUE_DELIMITERS VARCHAR

The delimiter to associate a name to its value.

OCCURRENCE FLOAT

The number of coincidences to look up for.

Returns¶

A varchar value with the same instring data.

Usage example¶

Input:

SELECT PUBLIC.NVP_UDF('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1);

Output:

orange chicken

Definition¶

Determines the short name of a month either in uppercase, lowercase, or capitalized.

PUBLIC.MONTH_SHORT_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the short name of the month.

RESULTCASE VARCHAR

The case result to be represented, accepted values are ‘uppercase’, ‘lowercase’, and ‘firstOnly’.

Returns¶

A varchar which is the short name of a month.

Usage example¶

Input:

SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'firstOnly');

Output:

OCT
oct
Oct

Definition¶

UDF that reproduces Teradata’s DATE-TO-NUMERIC functionality.

PUBLIC.DATE_TO_INT_UDF(DATE_TO_CONVERT DATE)

Parameters¶

DATE_TO_CONVERT DATE

The date is to be converted to an integer.

Returns¶

Returns the date in numeric values.

Example¶

Input:

SELECT mod(date '2015-11-26', 5890), sin(current_date);

CREATE TABLE SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE PERIOD(DATE)
);

REPLACE VIEW SAMPLE_VIEW
AS
SELECT
CAST(DATE_TYPE AS SMALLINT),
CAST(DATE_TYPE AS DECIMAL),
CAST(DATE_TYPE AS NUMBER),
CAST(DATE_TYPE AS FLOAT),
CAST(DATE_TYPE AS INTEGER)
FROM SAMPLE_TABLE;

Output:

SELECT
mod(PUBLIC.DATE_TO_INT_UDF(date '2015-11-26'), 5890),
sin(PUBLIC.DATE_TO_INT_UDF(CURRENT_DATE()));

CREATE TABLE PUBLIC.SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE VARCHAR(24) COMMENT 'PERIOD(DATE)' /*** MSC-WARNING - MSCEWI1036 - PERIOD DATA TYPE "PERIOD(DATE)" CONVERTED TO VARCHAR ***/
);

CREATE OR REPLACE VIEW PUBLIC.SAMPLE_VIEW
AS
SELECT
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE)
FROM PUBLIC.SAMPLE_TABLE;

Definition¶

User-defined function (UDF) that reproduces the P_INTERSECT operator to compare two or more period expressions and return the overlapping portion of the period expressions.

Generates a varchar representation of the two bounds of a period(TIMESTAMP, TIME or DATE) value, used to emulate the Teradata period value constructor function. This version generates the resulting string using the default format Snowflake has for representing PERIOD type values, if you require less or more precision either change the session parameter timestamp_output_format or use the three parameters version of this udf

More information about the source function here.

PERIOD_UDF(D1 TIMESTAMP_NTZ, D2 TIMESTAMP_NTZ)
PERIOD_UDF(D1 DATE, D2 DATE)
PERIOD_UDF(D1 TIME, D2 TIME)
PERIOD_UDF(D1 TIMESTAMP_NTZ, D2 TIMESTAMP_NTZ, PRECISIONDIGITS INT)
PERIOD_UDF(D1 TIME, D2 TIME, PRECISIONDIGITS INT)
PERIOD_UDF(D1 TIMESTAMP_NTZ)
PERIOD_UDF(D1 DATE)
PERIOD_UDF(D1 TIME)

Parameters¶

TIMESTAMP

The TimeStamp type.

TIME

The Time type.

DATE

The Date type.

PRECISIONDIGITS

The number of TIME digits to display.

Returns¶

Generates a VARCHAR representation of the PERIOD type

Usage example¶

Input:

SELECT
PERIOD_UDF('2005-02-03'),
PERIOD_UDF(date '2005-02-03'),
PERIOD_UDF(TIMESTAMP '2005-02-03 12:12:12.340000'),
PERIOD_UDF(TIMESTAMP '2005-02-03 12:12:12.340000');

Output:

2005-02-03*2005-02-04,
2005-02-03*2005-02-04,
2005-02-03 12:12:12.340000*2005-02-03 12:12:12.340001,
2005-02-03 12:12:12.340000*2005-02-03 12:12:12.340001

Definition¶

Returns the complete name of a day either in uppercase, lowercase, or capitalized.

PUBLIC.DAYNAME_LONG_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the dayname from.

RESULTCASE VARCHAR

The case that the result will present.

Returns¶

Returns a varchar with the complete name of a day.

Usage example¶

Input:

SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'firstOnly');

Output:

'TUESDAY'
'tuesday'
'Tuesday'

Definition¶

Process the day of the week a timestamp belongs to. It has the same behavior as the DAYNUMBER_OF_WEEK(DATE, 'COMPATIBLE') function.

PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the day of the week from.

Returns¶

Returns an integer between 1 and 7 where if January first is Wednesday then, 1 equals Wednesday, 2 equals Thursday… 7 equals Tuesday, and so on.

Usage example¶

Input:

SELECT PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(DATE '2022-01-01'),
PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(DATE '2023-05-05');

Output:

1, 6

Definition¶

Computes the Jaro-Winkler similarity between two input strings.

PUBLIC.JAROWINKLER_UDF (string1 VARCHAR, string2 VARCHAR)

Parameters¶

string1 VARCHAR

The input string

string2 VARCHAR

The input string

Returns¶

The function returns an integer between 0 and 1.

Usage example¶

Input:

SELECT PUBLIC.JAROWINKLER_UDF('święta', 'swieta')

Output:

0.770000

Definition¶

UDF that reproduces the TD_YEAR_BEGIN(Date, ‘ISO’) functionality. It depends on the DAYOFWEEKISO of PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF add or subtraction days to find the Monday closer to PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF.

PUBLIC.YEAR_BEGIN_ISO_UDF(INPUT DATE)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the first day of the year using the ISO calendar.

Returns¶

The first day of the year using the ISO calendar.

Usage example¶

Input:

SELECT  PUBLIC.YEAR_BEGIN_ISO_UDF(DATE '2022-01-01'),
PUBLIC.YEAR_BEGIN_ISO_UDF(DATE '2022-04-12');

Output:

2021-01-04, 2022-01-03

Definition¶

Process the year from a given date truncated to the specified number of digits.

PUBLIC.YEAR_PART_UDF(INPUT TIMESTAMP_TZ, DIGITS INTEGER)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the year from.

DIGITS INTEGER

The amount of digits wanted in the result.

Returns¶

Returns the year from a given date.

Usage example¶

Input:

SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 3);
SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 2);
SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 1);

Output:

'021'
'21'
'1'

Definition¶

Determines the year of a given date with a comma inserted between the first and second digits.

PUBLIC.YEAR_WITH_COMMA_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the year from.

Returns¶

Returns the year of a given date with a comma as varchar.

Usage example¶

Input:

SELECT PUBLIC.YEAR_WITH_COMMA_UDF(DATE '2021-10-26');

Output:

'2,021'

Definition¶

Reproduce the MONTHS_BETWEEN functionality

MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)

Parameters¶

FIRST_DATE TIMESTAMP_LTZ

The first or starting date to be processed in the function.

SECOND_DATE TIMESTAMP_LTZ

The second or ending date that determines the end of the count.

Returns¶

The number of months between the first and the second date.

Usage example¶

Input:

SELECT MONTHS_BETWEEN_UDF('2022-02-14', '2021-02-14');

Output:

12

Definition¶

Process how many seconds have passed since midnight for a given time.

PUBLIC.SECONDS_PAST_MIDNIGHT_UDF(INPUT TIME)

Parameters¶

INPUT TIME

The time to calculate how many seconds have passed since midnight.

Returns¶

A varchar that indicates how many seconds have passed since midnight.

Usage example¶

Input:

SELECT PUBLIC.SECONDS_PAST_MIDNIGHT_UDF(TIME'10:30:45');

Output:

'37845'

Definition¶

Returns the hexadecimal representation for a character string.

PUBLIC.CHAR2HEXINT_UDF(INPUT_STRING VARCHAR);

Parameters¶

INPUT_STRING VARCHAR

The string to be converted.

Returns¶

Returns the hexadecimal representation of the string.

Example¶

Input:

SELECT CHAR2HEXINT('1234') from t1;

Output:

SELECT
PUBLIC.CHAR2HEXINT_UDF('1234') from
t1;

More information from the source function¶

Function documentation can be checked here.

Definition¶

UDFs for the subtraction when one of its values is an interval and the other is a single access which is a reference to a column of type interval.

PUBLIC.INTERVAL_ADD_UDF
(INPUT_VALUE1 VARCHAR(), INPUT_PART1 VARCHAR(30), INPUT_VALUE2 VARCHAR(), INPUT_PART2 VARCHAR(30), OP CHAR, OUTPUT_PART VARCHAR())

Parameters¶

INPUT_VALUE1 VARCHAR

The value to be processed.

INPUT_PART1 VARCHAR

The time type, e.g. ‘HOUR’.

INPUT_VALUE2 VARCHAR

The column reference name, e.g. ‘INTERVAL_HOUR_TYPE’

INPUT_PART2 VARCHAR

The type of the column referenced.

OP CHAR

The sign that is being evaluated.

OUTPUT_PART VARCHAR

The output type.

Returns¶

A varchar resulting from the subtraction of the intervals.

Migration example¶

Input:

CREATE TABLE INTERVAL_TABLE
(
    INTERVAL_YEAR_TYPE INTERVAL YEAR
);

SELECT INTERVAL_YEAR_TYPE - INTERVAL '7' MONTH FROM INTERVAL_TABLE;

Output:

CREATE OR REPLACE TABLE INTERVAL_TABLE
(
    INTERVAL_YEAR_TYPE VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    PUBLIC.INTERVAL_ADD_UDF(INTERVAL_YEAR_TYPE, 'YEAR', '7', 'MONTH', '-', 'YEAR TO MONTH')
    FROM
    INTERVAL_TABLE;

Definition¶

User-defined function (UDF) that allows getting the complete day name given a timestamp.

PUBLIC.DAY_OF_WEEK_LONG_UDF(INPUT_DATE TIMESTAMP)

Parameters¶

INPUT_DATE TIMESTAMP

The date as timestamp to be processed as a complete day name.

Returns¶

The day name (in English).

Definition¶

User-defined function (UDF) works as equivalent to Teradata’s TD_WEEK_OF_CALENDAR function.

PUBLIC.TD_WEEK_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

Date to which the number of weeks since 1900-01-01 will be counted.

Returns¶

An integer that represents the number of weeks elapsed from 1900-01-01 to the input specified

Migration example¶

Input:

SELECT TD_WEEK_OF_CALENDAR(DATE '2023-11-30')

Output:

SELECT
PUBLIC.TD_WEEK_OF_CALENDAR_UDF(DATE '2023-11-30');

Definition¶

Wraps negative numbers between angle brackets instead of the minus sign. It is generated instead of a normal call to TO_CHAR if the PR format element was present in the original Teradata format.

PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(INPUT NUMBER, FORMATARG VARCHAR)

Parameters¶

INPUT NUMBER

The number to convert to varchar.

FORMATARG VARCHAR

The format argument that will be used to convert INPUT to varchar.

Returns¶

A varchar with negative numbers wrapped between angle brackets.

Usage example¶

Input:

SELECT PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(8456, '9999');
SELECT PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(-8456, '9999');

Output:

'8456'
'<8456>'

Definition¶

Searches the source_string argument for occurrences of searh_string.

PUBLIC.INSTR_UDF(SOURCE_STRING STRING, SEARCH_STRING STRING)

Parameters¶

SOURCE_STRING STRING

The string where the search will be conducted.

SEARCH_STRING STRING

The string that the function searches for.

Returns¶

The position in the source string where the occurrence is found (starting at 1).

Usage example¶

Input:

SELECT INSTR_UDF('INSTR FUNCTION','N');

Output:

2

Definition¶

Determines if a translation conversion can be performed without producing errors.

PUBLIC.TRANSLATE_CHK_UDF(COL_NAME STRING, SOURCE_REPERTOIRE_NAME STRING)

Parameters¶

COL_NAME STRING

The column to be checked.

SOURCE_REPERTOIRE_NAME STRING

The source repertoire name.

Returns¶

0: The string can be translated without error.
NULL: The string result is null.

Anything else: The position of the first character in the string causes a translation error.

Usage example¶

Input:

SELECT PUBLIC.TRANSLATE_CHK_UDF('ABC', 'UNICODE_TO_LATIN');

Output:

0

Definition¶

Reproduces the functionality of the expand-on function in Teradata.

PUBLIC.EXPAND_ON_UDF(TIME STRING, SEQ NUMBER, PERIOD STRING)

Parameters¶

TIME STRING

The expand-on time used in nachor.

SEQ NUMBER

The sequence to calculate each row.

PERIOD STRING

The date from the period required.

Returns¶

A VARCHAR that represents the calculation of the expand-on clause for the period.

Migration example¶

Input:

SELECT bg FROM table1 EXPAND ON pd AS bg BY ANCHOR ANCHOR_SECOND;

Output:

WITH
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, pd) bg
FROM
table1,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', pd))))
)
SELECT
bg
FROM
table1,
ExpandOnCTE;

Definition¶

Returns an array from 1 to the number returned in DIFF_TIME_PERIOD_UDF.

PUBLIC.ROW_COUNT_UDF(NROWS DOUBLE)

Parameters¶

NROWS DOUBLE

The number returned in DIFF_TIME_PERIOD_UDF.

Returns¶

An array to calculate how many rows are needed to simulate the expand-on clause.

Usage example¶

Input:

SELECT ROW_COUNT_UDF(DIFFTTIME_PERIOD('SECONDS','2022-11-26 10:15:20.000*2022-11-26 10:15:25.000'));

Output:

[1, 2, 3, 4, 5]

Migration example¶

Input:

SELECT NORMALIZE emp_id, duration FROM project EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND;

Output:

WITH ExpandOnCTE AS
(
SELECT
    PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
    project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
    duration
FROM
    project,
    ExpandOnCTE;

Definition¶

Determines to which century a date belongs.

PUBLIC.CENTURY_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to calculate the century.

Returns¶

Returns a varchar with the century from which a date comes.

Usage example¶

Input:

SELECT PUBLIC.CENTURY_UDF(DATE '1915-02-23');

Output:

'20'

Definition¶

Gets the difference between two times.

PUBLIC.TIME_DIFFERENCE_UDF
(MINUEND TIME, SUBTRAHEND TIME, INPUT_PART VARCHAR)

Parameters¶

MINUEND TIMESTAMP

Time subtracted from.

SUBTRAHEND TIMESTAMP

Time subtracted.

INPUT_PART VARCHAR

Parts to be returned.

EXTRACT_PART VARCHAR

Extract numeric value extracted from the interval.

Returns¶

A varchar value with the time.

Example¶

Input:

select extract(day from (timestampColumn1 - timestampColumn2 day to hour)) from tableName;

Output:

SELECT
EXTRACT_TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'DAY TO HOUR', 'DAY')
                                 from
tableName;

Definition¶

User-defined function (UDF) for the INTERVAL_DIVIDE function.

PUBLIC.INTERVAL_DIVIDE_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR(), INPUT_DIV INTEGER)

Parameters¶

INPUT_PART VARCHAR

The value of the division e.g. ‘YEAR TO MONTH’.

INPUT_VALUE VARCHAR

The interval that is divided to.

INPUT_DIV INTEGER

The numeric expression to be divided.

Returns¶

The result of an expression is a division between an interval and a numeric expression.

Migration example¶

Input:

SELECT INTERVAL '6-10' YEAR TO MONTH / 8;

Output:

SELECT
PUBLIC.INTERVAL_DIVIDE_UDF('YEAR TO MONTH', '6-10', 8);

Definition¶

The UDF calculates the day of the month of a specific timestamp. It has the behavior of the DAYNUMBER_OF_MONTH(DATE, ‘ISO’) function from Teradata.

PUBLIC.DAYNUMBER_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

A date to get the day of the week from.

Returns¶

An integer between 1 and 33.

Example¶

Input:

SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

Output:

SELECT
PUBLIC.DAYNUMBER_OF_MONTH_UDF(DATE'2022-12-22');

Definition¶

UDF that process the last day of December with ISO year. It is used to calculate the result of PUBLIC.YEAR_END_IDO_UDF.

PUBLIC.LAST_DAY_DECEMBER_OF_ISO_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the last day of December using ISO year.

Returns¶

A date which is the last day of December using ISO year.

Usage example¶

Input:

SELECT PUBLIC.LAST_DAY_DECEMBER_OF_ISO_UDF(DATE '2022-01-01');

Output:

2021-12-31

Definition¶

Helper function that adds two different dates

PUBLIC.DATE_ADD_UDF(FIRST_DATE DATE, SECOND_DATE DATE)

Parameters¶

FIRST_DATE DATE

The first date value that is to be added.

SECOND_DATE DATE

The second date value is to be added along with first_date.

¶

Returns¶

A date resulting from the addition of the two parameters.

Example¶

Input:

SELECT
    CAST(CAST (COLUMNB AS DATE FORMAT 'MM/DD/YYYY') AS TIMESTAMP(0))
    +
    CAST (COLUMNA AS TIME(0) FORMAT 'HHMISS' )
FROM TIMEDIFF;

Output:

SELECT
    PUBLIC.DATEADD_UDF(CAST(CAST(COLUMNB AS DATE) !!!RESOLVE EWI!!! /*** SSC-EWI-0033 - FORMAT 'MM/DD/YYYY' REMOVED, SEMANTIC INFORMATION NOT FOUND. ***/!!! AS TIMESTAMP(0)), PUBLIC.TO_INTERVAL_UDF(CAST(COLUMNA AS TIME(0)) !!!RESOLVE EWI!!! /*** SSC-EWI-0033 - FORMAT 'HHMISS' REMOVED, SEMANTIC INFORMATION NOT FOUND. ***/!!!))
    FROM
    TIMEDIFF;

Definition¶

User-defined function (UDF) that transforms a Julian Date (YYYYDDD) into a Gregorian date.

PUBLIC.JULIAN_TO_DATE_UDF(JULIAN_DATE CHAR(7))

Parameters¶

JULIAN_DATE CHAR

The Julian date to transform.

Returns¶

The date representation of the Julian date or null if conversion is not possible.

Usage example¶

Input:

SELECT JULIAN_TO_DATE_UDF('2022045');

Output:

'2022-02-14'

Migration example¶

Input:

SELECT TO_DATE('2020002', 'YYYYDDD');

Output:

SELECT
PUBLIC.JULIAN_TO_DATE_UDF('2020002');

Definition¶

The first day of January with ISO year which is used to calculate the result of the PUBLIC.YEAR_BEGIN_ISO_UDF function.

FUNCTION PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get January first using the ISO year.

Returns¶

A date which is the first day of January with ISO year.

Usage example¶

Input:

SELECT PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF(DATE '2022-01-01');

Output:

2021-01-01

Definition¶

UDF That reproduces DATETIME Subtraction

PUBLIC.TIMESTAMP_DIFFERENCE_UDF
(MINUEND TIMESTAMP, SUBTRAHEND TIMESTAMP, INPUT_PART VARCHAR)

Differences between Teradata and Snowflake date time subtraction¶

Teradata and Snowflake handle date time subtraction different, the syntax, returned type and precision are different.

• Syntax: The syntax used to handle DATE, TIMESTAMP and TIME subtraction operation in Teradata is with minus sign and interval used to specify the precision and format of operation result. For further details of syntax visit the following link https://docs.teradata.com/r/w19R4KsuHIiEqyxz0WYfgA/7kLLsWrP0kHxbk3iida0mA. In Snowflake subtraction of DATE, TIMESTAMP and TIME are handled by three different functions, DATEDIFF, TIMESTAMPDIFF, and TIMEDIFF respectively, these operations takes 3 parameters, the dates, and the date part to be returned. Nevertheless, DATEDIFF function will work with TIMESTAMP and TIME types The minus sign can be used if the operation is with DATE type, in this case, the default return type is the number of days.

• Return Type: In general the returned data type of Teradata are a different kind of Intervals. Intervals reference can be found at https://www.docs.teradata.com/r/T5QsmcznbJo1bHmZT2KnFw/z~5iW7rYVstcmNYbd6Dsjg. The data type returned by Snowflake when one of the functions named above is called is Integer representing the number of units. https://docs.snowflake.com/en/sql-reference/functions/datediff.html

• Rounding: To see when DATEDIFF (used in some cases UDF) uses the entire date or if it disregards of other parts in Snowflake visit the following link https://docs.snowflake.com/en/sql-reference/functions/datediff.html#usage-notes. This point could give different output than Teradata’s

Parameters¶

MINUEND TIMSTAMP

Date subtracted from.

SUBTRAHEND TIMESTAMP

Date subtracted.

INPUT_PART VARCHAR

Parts to be returned.

Returns¶

Formatted string value according to the INPUT_PART parameter.

Example¶

Input

select (timestampColumn1 - timestampColumn2 YEAR) from tableName;

Output:

SELECT
(
PUBLIC.TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'YEAR')) from
tableName;

Definition¶

The User-defined function (UDF) indicates the first day of a month in ISO formatting.

PUBLIC.FIRST_DAY_OF_MONTH_ISO_UDF(YEAR NUMBER, MONTH NUMBER)

Parameters¶

YEAR NUMBER

A number that represents the year.

MONTH NUMBER

A number that represents the month.

Returns¶

Returns the first day of the month in ISO formatting.

Example¶

Definition¶

UDF that reproduces Teradata’s NUMERIC-TO-DATE functionality

PUBLIC.INT_TO_DATE_UDF(NUMERIC_EXPRESSION INTEGER)

Parameters¶

NUMERIC_EXPRESSION INTEGER

A value that represents a DATE Type

Returns¶

Input number converted into a date data type.

Example¶

Input:

SELECT * FROM table1
WHERE date_column > 1011219

Output:

SELECT
* FROM
table1
WHERE date_column > PUBLIC.INT_TO_DATE_UDF( 1011219);

Definition¶

Converts data from zero to null to avoid problems with division by zero.

PUBLIC.NULLIFZERO_UDF(NUMBER_TO_VALIDATE NUMBER)

Parameters¶

NUMBER_TO_VALIDATE NUMBER

The input number to validate.

Returns¶

Null if the number is zero, otherwise the number.

Usage example¶

Input:

SELECT NULLIFZERO_UDF(0);

Output:

NULL

Definition¶

Emulates the format element DL in Teradata, which represents a date as ‘Day, Month DD, YYYY’.

PUBLIC.DATE_LONG_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to be represented in date long format.

Returns¶

A VARCHAR with the format element DL of Teradata.

Usage example¶

Input:

SELECT PUBLIC.DATE_LONG_UDF(DATE '2021-10-26');

Output:

'Tuesday, October 26, 2021'

Definition¶

User-defined function (UDF) works as equivalent to Teradata’s TD_MONTH_OF_CALENDAR function.

PUBLIC.TD_MONTH_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

Date to which the number of months since 1900-01-01 will be counted.

Returns¶

An integer that represents the number of months elapsed from 1900-01-01 to the input specified

Migration example¶

Input:

SELECT TD_MONTH_OF_CALENDAR(DATE '2023-11-30')

Output:

SELECT
PUBLIC.TD_MONTH_OF_CALENDAR_UDF(DATE '2023-11-30');

Definition¶

User-defined function (UDF) that allows getting the complete month name given a timestamp.

PUBLIC.MONTH_NAME_LONG_UDF(INPUT_DATE TIMESTAMP)

Parameters¶

INPUT DATE

The date as timestamp to be processed as a complete month name.

Returns¶

The month name (in English).

Definition¶

User-defined function (UDF) that reproduces the functionality of Teradata’s TO_DAY_OF_CALENDAR

PUBLIC.TD_DAY_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

Date to which the number of days since 1900-01-01 will be counted.

Returns¶

An integer that represents the number of days elapsed from 1900-01-01 to INPUT

Migration example¶

Input:

SELECT td_day_of_calendar(current_date)

Output:

SELECT
PUBLIC.TD_DAY_OF_CALENDAR_UDF(CURRENT_DATE());

Definition¶

User-defined function (UDF) that reproduces the Teradata PERIOD TO TIME casting behavior.

PERIOD_TO_TIME_UDF(PERIOD_VAL VARCHAR(22))

Parameters¶

PERIOD_VAL

The PERIOD value to transform.

Returns¶

The TIME representation of the PERIOD or null if conversion is not possible.

Usage example¶

Input:

SELECT PERIOD_TO_TIME_UDF(PERIOD_UDF(CURRENT_TIME()));

Output:

08:42:04

Definition¶

Searches the source_string argument for occurrences of searh_string.

PUBLIC.INSTR_UDF(SOURCE_STRING STRING, SEARCH_STRING STRING, POSITION DOUBLE, OCCURRENCE DOUBLE)

Parameters¶

SOURCE_STRING STRING

The string where the search will be conducted.

SEARCH_STRING STRING

The string that the function searches for.

POSITION DOUBLE

The character index where the search will start (starting at 1).

OCCURRENCE DOUBLE

The character index where the search will start (starting at 1).

Returns¶

The position in the source string where the occurrence is found.

Usage example¶

Input:

SELECT INSTR_UDF('CHOOSE A CHOCOLATE CHIP COOKIE','CH',2,2);

Output:

20

Definition¶

User-defined function (UDF) that processes the DATE_VALUE with the time portion of the day rounded to the unit specified by UNIT_TO_ROUND_BY. The equivalent function in Teradata is ROUND(date).

PUBLIC.ROUND_DATE_UDF(DATE_TO_ROUND TIMESTAMP_LTZ, UNIT_TO_ROUND_BY VARCHAR(5))

Parameters¶

DATE_TO_ROUND TIMESTAMP_TZ

The date to be rounded.

UNIT_TO_ROUND_BY VARCHAR

The unit to round the date to.

Returns¶

Returns the date rounded by the unit defined in UNIT_TO_ROUND_BY.

Migration example¶

Input:

SELECT ROUND(CURRENT_DATE, 'RM') RND_DATE

Output:

SELECT
PUBLIC.ROUND_DATE_UDF(CURRENT_DATE(), 'RM') RND_DATE;

Definition¶

Extracts a substring from a named string based on position.

PUBLIC.SUBSTR_UDF(BASE_EXPRESSION STRING, START_POSITION FLOAT, LENGTH FLOAT)

Parameters¶

BASE_EXPRESSION STRING

The expression from which the substring is to be extracted.

START_POSITION FLOAT

The starting position of the substring to extract from.

LENGTH FLOAT

The starting position of the substring to extract from.

Returns¶

The required substring.

Usage example¶

Input:

SELECT 
    PUBLIC.SUBSTR_UDF('ABC', -1, 1),
    PUBLIC.SUBSTR_UDF('ABC', -1, 2),
    PUBLIC.SUBSTR_UDF('ABC', -1, 3),
    PUBLIC.SUBSTR_UDF('ABC', 0, 1),
    PUBLIC.SUBSTR_UDF('ABC', 0, 2);

Output:

'','','A','','A'

Definition¶

Retrieves the value associated with a name inside the name-value pairs of the transaction, session, or profile query band.

GETQUERYBANDVALUE_UDF(SEARCHNAME VARCHAR)

Parameters¶

SEARCHNAME VARCHAR

The name key to search in the pairs.

Returns¶

The value associated with the name key inside the session query band or null if not found.

Usage example¶

Input:

ALTER SESSION SET QUERY_TAG = 'user=Tyrone;role=security';
SELECT GETQUERYBANDVALUE_UDF('role');

Output:

security

Migration example¶

Input:

SELECT GETQUERYBANDVALUE(1, 'group');

Output:

/** MSC-ERROR - MSCEWI2084 - TRANSACTION AND PROFILE LEVEL QUERY TAGS NOT SUPPORTED IN SNOWFLAKE, REFERENCING SESSION QUERY TAG INSTEAD **/
SELECT GETQUERYBANDVALUE_UDF('group');

Definition¶

User-defined function (UDF) that determines which “full week” of the year is based on a date. It is equivalent to the TD_WEEK_OF_YEAR and the WEEKNUMBER_OF_YEAR functions from Teradata.

PUBLIC.TD_WEEK_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

Date to get the number of week from.

Returns¶

A number that represents the week number the date belongs to.

Usage example¶

Input:

SELECT PUBLIC.WEEK_OF_YEAR_UDF(DATE '2024-05-10'),
PUBLIC.WEEK_OF_YEAR_UDF(DATE '2020-01-03')

Output:

18, 0

Definition¶

Extracts ‘Data Part’ from the result of the subtraction MINUEND - SUBTRAHEND

PUBLIC.EXTRACT_TIMESTAMP_DIFFERENCE_UDF
(MINUEND TIMESTAMP, SUBTRAHEND TIMESTAMP, INPUT_PART VARCHAR, EXTRACT_PART VARCHAR)

Differences between Teradata and Snowflake date-time extraction¶

Teradata and Snowflake differ in the parameters that each function respectively might receive and the return type.

• Parameters: The main difference between Teradata and Snowflake extract function is that Snowflake does not handle intervals, it only supports dates and times. For further information on this, read Snowflake’s EXTRACT function documentation and Teradata’s EXTRACT function documentation.

• Return type: Teradata EXTRACT function returns an integer or decimal(8, 2) value representing the part requested and Snowflake returns a number value representing a date-time of the part requested.

Teradata and Snowflake differ in the parameters that each function respectively might receive and the return type.

Parameters¶

MINUEND TIMESTAMP

Date subtracted from.

SUBTRAHEND TIMESTAMP

Date subtracted.

INPUT_PART VARCHAR

Formatted varchar, is the original requested part (same as TIMESTAMP_DIFERENCE INPUT_PART) and must be one of these:

• 'DAY TO HOUR'

• 'DAT TO MINUTE'

• 'DAY TO SECOND'

• 'DAY TO MINUTE'

• 'HOUR TO MINUTE'

• 'HOUR TO SECOND'

• 'MINUTE TO SECOND'

EXTRACT_PART VARCHAR

Value to be extracted, the request part should be contained in the input part interval, must be 'DAY', 'HOUR', 'MINUTE' or 'SECOND'.

Returns¶

The number of requests part of the extract.

Example¶

Input:

select extract(day from (timestampColumn1 - timestampColumn2 day to hour)) from tableName;

Output:

SELECT
EXTRACT_TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'DAY TO HOUR', 'DAY')
from
tableName;

Definition¶

User-defined function (UDF) that reproduces dot notation used to querying of values inside JSON objects.

JSON_EXTRACT_DOT_NOTATION_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING)

Differences between Teradata JSON Entity Reference (dot notation ) and Snowflake JSON query method.¶

Teradata and Snowflake differ in the methods used for traverse JSON data. In this case Teradata use a way based on JavaScript that use dot notation, array indexing, and other especial operators like wildcard access or double dot. On the other hand, snowflake doesn’t support these operators, and it only can access members and arrays.

Parameters¶

JSON_OBJECT VARIANT

The JSON variable from which to extract the values.

JSON_PATH STRING

The JSON path that indicates where the values are located inside the JSON_OBJECT.

Returns¶

The values specified by the JSON_PATH inside the JSON_OBJECT.

Migration example¶

Input:

SELECT CAST(varcharColumn AS JSON(2000))..name FROM variantTest;

Output:

SELECT
JSON_EXTRACT_DOT_NOTATION_UDF(CAST(varcharColumn AS VARIANT), '$..name')
FROM
variantTest;

Definition¶

Defines in which week of the month a given date is.

PUBLIC.WEEK_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get which week of the month it belongs to.

Returns¶

A varchar that showcases the week of a month of the date.

Usage example¶

Input:

SELECT PUBLIC.WEEK_OF_MONTH_UDF(DATE '2021-10-26');

Output:

'4'

Definition¶

UDF that overloads the function DAYNAME_LONG_UDF and applies the default case (capitalized).

PUBLIC.DAYNAME_LONG_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to take the dayname of.

Returns¶

Returns a varchar with the complete name of a day.

Usage example¶

Input:

SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2022-06-30');

Output:

'Thursday'

Definition¶

Transforms interval to month.

PUBLIC.INTERVAL_TO_MONTHS_UDF
(INPUT_VALUE VARCHAR())

Parameters¶

INPUT_VALUE VARCHAR

The interval to be converted to month.

Returns¶

An integer with the processed interval as months.

Definition¶

Retrieves the value associated with a name inside the name-value pairs of the transaction, session, or profile query band.

GETQUERYBANDVALUE_UDF(QUERYBAND VARCHAR, SEARCHTYPE FLOAT, SEARCHNAME VARCHAR)

Parameters¶

QUERYBAND VARCHAR

The query band which can be a concatenation of transaction, session, and profile query bands.

SEARCHTYPE FLOAT

The level of the pairs that will be searched for a match.

0 = Any.

1 = Transaction.

2 = Session.

3 = Profile.

SEARCHNAME VARCHAR

The name key to search in the pairs.

Returns¶

The value associated with the name key at the specific level or null if not found.

Usage example¶

Input:

SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 2, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'role');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 1, 'role');

Output:

      Matt
      SaraDB
      DbAdmin
      NULL

Migration example¶

Input:

SELECT GETQUERYBANDVALUE('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account')

Output:

WITH
--** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
duration
FROM
project,
ExpandOnCTE;

Definition¶

Calculates the Julian day of a given date, it is the amount of days that have passed since January 1, 4713 BC.

PUBLIC.JULIAN_DAY_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date whose Julian day will be calculated.

Returns¶

A varchar which is the Julian date calculated in the function.

Usage example¶

Input:

SELECT PUBLIC.JULIAN_DAY_UDF(DATE '2021-10-26');

Output:

'2459514'

Definition¶

Determine which month of the year a date belongs to.

PUBLIC.WEEKNUMBER_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to het the number of month from.

Returns¶

A number that represents the month number that the date belongs to.

Usage example¶

Input:

SELECT PUBLIC.WEEKNUMBER_OF_MONTH_UDF(DATE '2022-05-21')

Output:

3

Definition¶

User-defined function (UDF) that reproduces JSONExtract, JSONExtractValue, and JSONExtractLargeValue functions to extract multiple values out of a JSON variable.

JSON_EXTRACT_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING, SINGLE_VALUE BOOLEAN)

Parameters¶

JSON_OBJECT VARIANT

The JSON variable from which to extract the values.

JSON_PATH STRING

The JSON path that indicates where the values are located inside the JSON_OBJECT.

SINGLE_VALUE BOOLEAN

BOOLEAN if true, it returns only one value (necessary for JSONExtractValue AND JSONExtractLargeValue), otherwise returns an array (JSONExtract).

Returns¶

The values specified by the JSON_PATH inside the JSON_OBJECT.

Migration example¶

Input:

SELECT
    Store.JSONExtract('$..author') as AllAuthors
FROM BookStores;

Output:

SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors
    FROM
    BookStores;

Definition¶

Calculates the expand-on function depending on the period type argument.

PUBLIC.COMPUTE_EXPAND_ON_UDF(TIME STRING, SEQ NUMBER, PERIOD TIMESTAMP, PERIODTYPE STRING)

Parameters¶

TIME STRING

The time used in anchor.

SEQ NUMBER

The sequence to calculate each row.

PERIOD TIMESTAMP

The date from the period required.

PERIODTYPE STRING

The period type for the calculation (’BEGIN’ or ‘END’)

Returns¶

A timestamp that represents each row of the EXPAND-ON.

Example¶

Definition¶

Returns the week number from the start of the quarter to the specified date. It has the same behavior as the WEEKNUMBER_OF_QUARTER(DATE, 'ISO') function with Teradata calendar.

PUBLIC.WEEK_NUMBER_OF_QUARTER_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The date to get the week number of the quarter.

Returns¶

An integer that represents the number of the week in the quarter.

Usage example¶

Input:

SELECT WEEK_NUMBER_OF_QUARTER_UDF(DATE '2023-01-01'),
WEEK_NUMBER_OF_QUARTER_UDF(DATE '2022-10-27')

Output:

1, 4

Definition¶

User-defined function (UDF) that reproduces the TD_YEAR_END( date, ‘ISO’) functionality from Teradata.

PUBLIC.YEAR_END_ISO_UDF(INPUT date)

Parameters¶

INPUT DATE

The date to get the last day of the year using ISO calendar.

Returns¶

The last day of the year using ISO calendar.

Usage example¶

Input:

SELECT  PUBLIC.YEAR_END_ISO_UDF(DATE '2022-01-01'),
PUBLIC.YEAR_END_ISO_UDF(DATE '2022-04-12');

Output:

2022-01-02, 2023-01-01

Definition¶

Insert the specified currency text at the given position in the number, or before the first digit to avoid leaving blanks or the number sign between the currency and the number.

PUBLIC.INSERT_CURRENCY_UDF(INPUT VARCHAR, CURRENCYINDEX INTEGER, CURRENCYVALUE VARCHAR)

Parameters¶

INPUT VARCHAR

The result of the TO_CHAR(Numeric) that needs the currency inserted.

CURRENCYINDEX INTEGER

The index to insert the currency at.

CURRENCYVALUE VARCHAR

The text that will be inserted as currency.

Returns¶

A varchar which has the currency text at a specific position.

Usage example¶

Input:

SELECT PUBLIC.INSERT_CURRENCY_UDF(to_char(823, 'S999999'), '1', 'CRC');

Output:

'+CRC823'

Definition¶