Cast to INTERVAL datatype

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

Description

Snowflake does not support the Interval data type, but it has INTERVAL constants that can be used in DateTime operations, SnowConvert will transform CAST functions of literal values to the INTERVAL datatype into an equivalent INTERVAL constant or TIMESTAMPADD function only if these constants are being used in DateTime operations.

Sample Source Patterns

Interval literals used in DateTime operations

Teradata

Query

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;

Result

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

Snowflake

Query

SELECT
TIMESTAMP '2022-10-15 10:30:00' + INTERVAL '12 hour, 34 min, 56.78 sec' AS VARCHAR_TO_INTERVAL,
TIMESTAMPADD(YEAR, -5, TIMESTAMP '2022-10-15 10:30:00') AS NUMBER_TO_INTERVAL;

Result

VARCHAR_TO_INTERVAL     | NUMBER_TO_INTERVAL     |
------------------------+------------------------+
2022-10-15 23:04:57.000 |2017-10-15 10:30:00.000 |

Known Issues

1. Uses of INTERVAL casts are not supported outside of DateTime operations

Since Snowflake does not support the INTERVAL, SnowConvert will warn the user that the datatype is not supported when used outside of DateTime operations.

2. Casts of non-literal values to INTERVAL are not supported

To create the necessary INTERVAL constant or TIMESTAMPADD function SnowConvert requires the value being cast to be a literal, if it is not the case then the cast will be marked as INTERVAL datatype not supported.

Related EWIs

1. MSCEWI2002: Interval type not supported.