SSC-FDM-TD0004

Period types are handled as two data fields

Description

Teradata has a period data type used to represent a time interval, with instances of this type having a beginning and ending bound of the same type (time, date or timestamp) along with a set of functions that allow to initialize and manipulate period data like PERIOD, BEGIN, END, OVERLAPS, etc.

Since the period type is not supported by Snowflake, SnowConvert transforms this type and its related functions using the following rules:

• Any period type declaration in column tables is migrated as a two column of the same type.

• The period value constructor function is migrated into two different constructors of the period subtype one with the begin value and the other with the end value.

• Supported functions that expect period type parameters are migrated to UDFs as well, these UDFs expect almost two parameters for the begin value and the end value.

Example code

Input code:

IN -> Teradata_01.sql

-- Additional Params: --SplitPeriodDatatype
CREATE TABLE DateTable
(
	COL1 PERIOD(DATE) DEFAULT PERIOD (DATE '2005-02-03', UNTIL_CHANGED)
);

Output code:

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE DateTable
(
	COL1_begin DATE DEFAULT DATE '2005-02-03',
	COL1_end DATE DEFAULT DATE '9999-12-31' /*** SSC-FDM-TD0004 - PERIOD DATA TYPES ARE HANDLED AS TWO DATA FIELDS ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Recommendations

• If you need more support, you can email us at [email protected]