SSC-EWI-TD0053

Snowflake does not support the period datatype, all periods are handled as varchar instead

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

Precision of generated varchar representations

PERIOD_UDF generates the varchar representation of a period using the default formats for timestamps and time specified in Snowflake, this means timestamps will have three precision digits and time variables will have zero, because of this you may find that the results have a higher/lower precision from the expected, there are two options to modify how many precision digits are included in the resulting string:

• Use the three parameters version of PERIOD_UDF: This overload of the function takes thePRECISIONDIGITSparameter, an integer between 0 and 9 to control how many digits of the fractional time part will be included in the result. Note that even if Snowflake supports up to nine digits of precision the maximum in Teradata is six. Example:

Call	Result
PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 0)	'13:30:45*15:35:20'
PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 2)	'13:30:45.87*15:35:20.34'
PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 5)	'13:30:45.87055*15:35:20.34489'

• Alter the session parameters TIMESTAMP_NTZ_OUTPUT_FORMAT and TIME_OUTPUT_FORMAT: The commands ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = <format> andALTER SESSION SET TIME_OUTPUT_FORMAT = <format>

  can be used to modify the formats Snowflake uses by default for the current session, modifying them to include the desired number of precision digits changes the result of future executions of PERIOD_UDF for the current session.

Example code

Input code:

IN -> Teradata_01.sql

create table vacations (
    employeeName varchar(50),
    duration period(date)
);

insert into vacations values ('Richard', period(date '2021-05-15', date '2021-06-15'));

select end(duration) from vacations;

Output code:

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE vacations (
    employeeName varchar(50),
    duration VARCHAR(24) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO vacations
VALUES ('Richard', PUBLIC.PERIOD_UDF(date '2021-05-15', date '2021-06-15') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!);

SELECT
    PUBLIC.PERIOD_END_UDF(duration) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!! from
    vacations;

Recommendations

• Since the behavior ofPERIODand its related functions is emulated using varchar, we recommend reviewing the results obtained to ensure its correctness.

• If you need more support, you can email us at [e-mail]