PERIOD_UDF

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