Cast to INTERVAL datatype

Translation specification for the transformation of casts to the INTERVAL datatype to Snowflake

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

Snowflake does not support the Interval data type, but it has INTERVAL constants that can be used in DateTime operations and other uses can be emulated using VARCHAR, SnowConvert will transform CAST functions to the INTERVAL datatype into an equivalent depending on the case:

• When the value being casted is of type interval an UDF will be generated to produce the new interval equivalent as a string

• When the value is a literal, an Snowflake interval constant will be generated if the cast is used in a datetime operation, otherwise a literal string will be generated

• When the value is non-literal then a cast to string will be generated

Sample Source Patterns

Non-interval literals

Teradata

Query

IN -> Teradata_01.sql

SELECT TIMESTAMP '2022-10-15 10:30:00' + CAST ('12:34:56.78' AS INTERVAL HOUR(2) TO SECOND(2)) AS VARCHAR_TO_INTERVAL,
TIMESTAMP '2022-10-15 10:30:00' + CAST(-5 AS INTERVAL YEAR(4)) AS NUMBER_TO_INTERVAL,
CAST('07:00' AS INTERVAL HOUR(2) TO MINUTE) AS OUTSIDE_DATETIME_OPERATION;

Result

VARCHAR_TO_INTERVAL | NUMBER_TO_INTERVAL | OUTSIDE_DATETIME_OPERATION |
--------------------+--------------------+----------------------------+
2022-10-15 23:04:56 |2017-10-15 10:30:00 | 7:00                       |

Snowflake

Query

OUT -> Teradata_01.sql

SELECT
TIMESTAMP '2022-10-15 10:30:00' + INTERVAL '12 HOUR, 34 MINUTE, 56 SECOND, 780000 MICROSECOND' AS VARCHAR_TO_INTERVAL,
TIMESTAMP '2022-10-15 10:30:00' + INTERVAL '-5 YEAR' AS NUMBER_TO_INTERVAL,
'07:00' AS OUTSIDE_DATETIME_OPERATION;

Result

VARCHAR_TO_INTERVAL     | NUMBER_TO_INTERVAL     | OUTSIDE_DATETIME_OPERATION |
------------------------+------------------------+----------------------------+
2022-10-15 23:04:56.780 |2017-10-15 10:30:00.000 | 07:00                      |

Non-literal and non-interval values

Teradata

Query

IN -> Teradata_02.sql

SELECT TIMESTAMP '2022-10-15 10:30:00' + CAST('20 ' || '10' AS INTERVAL DAY TO HOUR) AS DATETIME_OPERATION,
CAST('20 ' || '10' AS INTERVAL DAY TO HOUR) AS OUTSIDE_DATETIME_OPERATION;

Result

DATETIME_OPERATION  | OUTSIDE_DATETIME_OPERATION |
--------------------+----------------------------+
2022-11-04 20:30:00 | 20 10                      |

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
PUBLIC.DATETIMEINTERVALADD_UDF(TIMESTAMP '2022-10-15 10:30:00', CAST('20 ' || '10' AS VARCHAR(21)), 'DAY', '+') AS DATETIME_OPERATION,
CAST('20 ' || '10' AS VARCHAR(21)) AS OUTSIDE_DATETIME_OPERATION;

Result

DATETIME_OPERATION      | OUTSIDE_DATETIME_OPERATION |
------------------------+----------------------------+
2022-11-04 20:30:00.000 | 20 10                      |

Cast of interval to another interval

Teradata

Query

IN -> Teradata_03.sql

SELECT
TIMESTAMP '2022-10-15 10:30:00' + CAST(INTERVAL '5999' MINUTE AS INTERVAL DAY TO HOUR) AS DATETIME_OPERATION,
CAST(INTERVAL '5999' MINUTE AS INTERVAL DAY TO HOUR) AS OUTSIDE_DATETIME_OPERATION;

Result

DATETIME_OPERATION  | OUTSIDE_DATETIME_OPERATION |
--------------------+----------------------------+
2022-10-19 13:30:00 | 4 03                       |

Snowflake

Query

OUT -> Teradata_03.sql

SELECT
PUBLIC.DATETIMEINTERVALADD_UDF(
TIMESTAMP '2022-10-15 10:30:00', PUBLIC.INTERVALTOINTERVAL_UDF('5999', 'MINUTE', 'MINUTE', 'DAY', 'HOUR'), 'DAY', '+') AS DATETIME_OPERATION,
PUBLIC.INTERVALTOINTERVAL_UDF('5999', 'MINUTE', 'MINUTE', 'DAY', 'HOUR') AS OUTSIDE_DATETIME_OPERATION;

Result

DATETIME_OPERATION      | OUTSIDE_DATETIME_OPERATION |
------------------------+----------------------------+
2022-10-19 13:30:00.000 | 4 03                       |

Known Issues

No known issues.

Related EWIs

No related EWIs.