SnowConvert: Function References - Shared¶

Definition¶

This user-defined function (UDF) is used to multiply an interval of time with a value of ‘N’ times.

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

Parameters¶

INPUT_PART VARCHAR

The format of the operation. E.g.: DAY, HOUR TO SECOND, YEAR TO MONTH.

INPUT_VALUE VARCHAR

The interval of time to be multiplied.

INPUT_MULT INTEGER

The time to multiply the interval of time.

Returns¶

Returns a varchar with the result of the multiplication.

Usage example¶

Input:

SELECT INTERVAL_MULTIPLY_UDF('DAY', '2', 100);

Output:

200

Definition¶

This user-defined function (UDF) reproduces the Teradata and Oracle TRUNC(Date) functionality when the format parameter is specified.

TRUNC_UDF(DATE_TO_TRUNC TIMESTAMP_LTZ, DATE_FMT VARCHAR(5))

Parameters¶

DATE_TO_TRUNC TIMESTAMP_LTZ

A timestamp_ltz value to truncate which must be a date, timestamp, or timestamp with timezone.

DATE_FMT VARCHAR

A varchar value that should be one of the date formats supported by the trunc function.

Returns¶

Returns a date truncated using the format specified.

Usage example¶

Input:

SELECT TRUNC_UDF(TIMESTAMP '2015-08-18 12:30:00', 'Q')

Output:

2015-07-01

Definition¶

This user-defined function (UDF) is used to determine the quantity of seconds from an interval which is also correlated to the processed time type. This is an auxiliary function.

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

Parameters¶

INPUT_PART VARCHAR

The related type of the second parameter. E.g. DAY, DAY TO HOUR, HOUR, MINUTE.

INPUT_VALUE VARCHAR

The value to be converted to seconds.

Returns¶

Returns a decimal value type with the number of seconds.

Usage example¶

Input:

SELECT INTERVAL_TO_SECONDS_UDF('DAY', '1');

Output:

86400.000000

Definition¶

This user-defined function (UDF) is used to generate the difference between an interval value and a date.

DATEDIFF_UDF(D DATE, INTERVAL_VALUE STRING)

Parameters¶

D DATE

The date to be used to process the difference with the interval.

INTERVAL_VALUE STRING

The interval value that will be used to create the difference from.

Returns¶

Returns a date with the resulting value of the subtraction of time.

Usage example¶

Input:

SELECT DATEDIFF_UDF('2024-01-30', 'INTERVAL ''2-1'' YEAR(2) TO MONTH');

Output:

2021-12-30

Definition¶

This user-defined function (UDF) is used to transform seconds into intervals. This is an auxiliary function.

SECONDS_TO_INTERVAL_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE NUMBER)

Parameters¶

INPUT_PART VARCHAR

The related type of the second parameter. E.g. DAY, DAY TO HOUR, HOUR, MINUTE, MINUTE TO SECOND.

INPUT_VALUE VARCHAR

The seconds to be converted to intervals.

Returns¶

Returns

Usage example¶

Input:

SELECT SECONDS_TO_INTERVAL_UDF('DAY TO SECOND', '86400');

Output:

1 000:000:000

Definition¶

This user-defined function (UDF) is used to add a date with an interval of time.

DATEADD_UDF(INTERVAL_VALUE STRING,D DATE)

Parameters¶

INTERVAL_VALUE STRING

The interval of time to be added.

D DATE

The date to be added with the interval of time.

Returns¶

Returns a date with the addition of the interval of time and the date.

Usage example¶

Input:

SELECT DATEADD_UDF('INTERVAL ''2-1'' YEAR(2) TO MONTH', '2024-01-30');

Output:

2026-02-28

Definition¶

This user-defined function (UDF) is used to generate the difference between an interval value and a date.

DATEDIFF_UDF(INTERVAL_VALUE STRING,D DATE)

Parameters¶

INTERVAL_VALUE STRING

The interval value that will be used to create the difference from.

D DATE

The date to be used to process the difference with the interval.

Returns¶

Returns a date with the resulting value of the subtraction of time.

Usage example¶

Input:

SELECT DATEDIFF_UDF('INTERVAL ''2-1'' YEAR(2) TO MONTH', '2024-01-30');

Output:

2021-12-30

Definition¶

This user-defined function (UDF) is used to add a date with an interval of time.

DATEADD_UDF(D DATE, INTERVAL_VALUE STRING)

Parameters¶

D DATE

The date to be added with the interval of time.

INTERVAL_VALUE STRING

The interval of time to be added.

Returns¶

Returns a date with the addition of the interval of time and the date.

Usage example¶

Input:

SELECT DATEADD_UDF('2024-01-30', 'INTERVAL ''1-1'' YEAR(2) TO MONTH');

Output:

2025-02-28

Definition¶

This user-defined function (UDF) is used to generate a separate interval of time from the current time.

TO_INTERVAL_UDF(D2 TIME)

Parameters¶

D2 TIME

The input time to converts into a separate interval.

Returns¶

Returns a string with the information of the input time separated.

Usage example¶

Input:

SELECT TO_INTERVAL_UDF(CURRENT_TIME);

Output:

INTERVAL '4 HOURS,33 MINUTES,33 SECOND'

Definition¶

This user-defined function (UDF) is used to generate an integer with the quantity of a month from an interval. This is an auxiliary function.

INTERVAL_TO_MONTHS_UDF
(INPUT_VALUE VARCHAR())

Parameters¶

INPUT_VALUE VARCHAR

The interval value to be transformed into months.

Returns¶

Returns an integer with the processed information about months.

Usage example¶

Input:

SELECT PUBLIC.INTERVAL_TO_MONTHS_UDF('1-6');

Output:

18

Definition¶

This user-defined function (UDF) is used to subtract an interval of time with a timestamp.

DATEADD_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)

Parameters¶

INTERVAL_VALUE STRING

The interval of time to be subtracted.

D TIMESTAMP

The timestamp to be subtracted with the interval of time.

Returns¶

Returns a date with the subtraction of the interval of time and the date.

Usage example¶

Input:

SELECT PUBLIC.DATEDIFF_UDF('INTERVAL ''1-1'' YEAR(2) TO MONTH', TO_TIMESTAMP('2024-01-31 05:09:09.799 -0800'));

Output:

2022-12-31 05:09:09.799

Definition¶

This user-defined function (UDF) is used to transform month values to intervals. This is an auxiliary function.

MONTHS_TO_INTERVAL_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE NUMBER)

Parameters¶

INPUT_PART VARCHAR

The related type of the second parameter. E.g. YEAR TO MONTH, YEAR, MONTH.

INPUT_VALUE VARCHAR

The month to be converted to intervals.

Returns¶

Returns a varchar with the input value transform to an interval.

Usage example¶

Input:

SELECT MONTHS_TO_INTERVAL_UDF('YEAR TO MONTH', 2);

Output:

2

Definition¶

This user-defined function (UDF) is used to subtract a timestamp with an interval of time.

DATEDIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)

Parameters¶

D TIMESTAMP

The timestamp that will be subtracted with the interval of time.

INTERVAL_VALUE STRING

The interval of time to be subtracted.

Returns¶

Returns a date with the subtraction of the interval of time and the date.

Usage example¶

Input:

SELECT PUBLIC.DATEDIFF_UDF(TO_TIMESTAMP('2024-01-31 05:09:09.799 -0800'), 'INTERVAL ''1-1'' YEAR(2) TO MONTH');

Output:

2022-12-31 05:09:09.799

Definition¶

This user-defined function (UDF) reproduces the Teradata and Oracle TRUNC(Numeric) functionality when a scale is not specified.

TRUNC_UDF(INPUT NUMBER)

Parameters¶

INPUT NUMBER

The number to truncate.

Returns¶

Returns an int as the input truncated to zero decimal places.

Usage example¶

Input:

SELECT TRUNC_UDF(25122.3368)

Output:

25122

Definition¶

This user-defined function (UDF) reproduces the Teradata and Oracle TRUNC(Numeric) functionality when a scale is specified.

TRUNC_UDF(INPUT NUMBER, SCALE NUMBER)

Parameters¶

INPUT NUMBER

The number to truncate.

SCALE NUMBER

The amount of places to truncate (between -38 and 38).

Returns¶

Returns an int as the input truncated to scale places.

Usage example¶

Input:

SELECT TRUNC_UDF(25122.3368, -2);

Output:

25100

Definition¶

This user-defined function (UDF) is used to add or subtract intervals with a specific time type.

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 quantity referenced to a time type.

INPUT_PART1 VARCHAR

The time type of the INPUT_VALUE1. E.g.: HOUR.

INPUT_VALUE2 VARCHAR

The second quantity referenced to a time type.

INPUT_PART2 VARCHAR

The time type of the INPUT_VALUE2. E.g.: HOUR.

OP CHAR

The operation. I can be a ‘+’ or a ‘-‘.

OUTPUT_PART VARCHAR

The time type of the output operation.

Returns¶

Returns a varchar with the result of the indicated operation and values.

Usage example¶

Input:

SELECT INTERVAL_ADD_UDF('7', 'HOUR', '1', 'HOUR', '+', 'HOUR');

Output:

8

Definition¶

This user-defined function (UDF) is used to add a timestamp with an interval of time.

DATEADD_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)

Parameters¶

INTERVAL_VALUE STRING

The interval of time to be added.

D TIMESTAMP

The timestamp to be added with the interval of time.

Returns¶

Returns a date with the addition of the interval of time and the date.

Usage example¶

Input:

SELECT PUBLIC.DATEADD_UDF('INTERVAL ''1-1'' YEAR(2) TO MONTH', TO_TIMESTAMP('2024-01-31 05:09:09.799 -0800'));

Output:

2025-02-28 05:09:09.799

Definition¶

This user-defined function (UDF) reproduces the Teradata and Oracle TRUNC(Date) functionality when the format parameter is not specified.

TRUNC_UDF(INPUT TIMESTAMP_LTZ)

Parameters¶

DATE_TO_TRUNC TIMESTAMP_LTZ

A timestamp_ltz value to truncate which must be a date, timestamp, or timestamp with timezone.

Returns¶

Returns a date part of DATE_TO_TRUNC.

Usage example¶

Input:

SELECT TRUNC_UDF(TIMESTAMP '2015-08-18 12:30:00')

Output:

2015-08-18

Definition¶

This user-defined function (UDF) is used to add a timestamp with an interval of time.

DATEADD_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)

Parameters¶

D TIMESTAMP

The timestamp to be added with the interval of time.

INTERVAL_VALUE STRING

The interval of time to be added.

Returns¶

Returns a date with the addition of the interval of time and the date.

Usage example¶

Input:

SELECT PUBLIC.DATEADD_UDF(TO_TIMESTAMP('2024-01-31 05:09:09.799 -0800'), 'INTERVAL ''1-1'' YEAR(2) TO MONTH');

Output:

2025-02-28 05:09:09.799