TIMESTAMP_DIFFERENCE_UDF

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

Some operations may produce different day outputs with an error range of 1 unit.

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

IN -> Teradata_01.sql

select (timestampColumn1 - timestampColumn2 YEAR) from tableName;

Output:

OUT -> Teradata_01.sql

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