EXTRACT_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.

Some parts of the output code are omitted for clarity reasons.

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:

IN -> Teradata_01.sql

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

Output:

OUT -> Teradata_01.sql

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