SSC-FDM-TD0004

Period types are handled as two data fields

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.

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

Last updated