MSCEWI3095

Operation Between Interval Type and Date Type not Supported

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are not a supported data type, they are transformed to VARCHAR(20). Therefore all arithmetic operations between Date Types and the original Interval Type Columns are not supported.

Furthermore, operations between an Interval Type and Date Type (in this order) are not supported in Snowflake; and these operations use this EWI as well.

Example Code

Input Code:

CREATE TABLE table_with_intervals
(
    date_col DATE,
    time_col TIMESTAMP,
    intervalYearToMonth_col INTERVAL YEAR TO MONTH,
    intervalDayToSecond_col INTERVAL DAY TO SECOND
);

-- Date + Interval Y to M
SELECT date_col + intervalYearToMonth_col FROM table_with_intervals;

-- Date - Interval D to S
SELECT date_col - intervalDayToSecond_col FROM table_with_intervals;

-- Timestamp + Interval D to S
SELECT time_col + intervalDayToSecond_col FROM table_with_intervals;

-- Timestamp - Interval Y to M
SELECT time_col - intervalYearToMonth_col FROM table_with_intervals;

Output Code:

CREATE OR REPLACE TABLE PUBLIC.table_with_intervals (
date_col DATE,
time_col TIMESTAMP(6),
intervalYearToMonth_col VARCHAR(20) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE "YEAR TO MONTH" CONVERTED TO VARCHAR ***/,
intervalDayToSecond_col VARCHAR(20) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE "DAY TO SECOND" CONVERTED TO VARCHAR ***/);

-- Date + Interval Y to M
SELECT
/*** MSC-ERROR - MSCEWI3095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/
PUBLIC.INTERVAL_OPERATION_UDF('date_col + intervalYearToMonth_col')
FROM PUBLIC.table_with_intervals;

-- Date - Interval D to S
SELECT
/*** MSC-ERROR - MSCEWI3095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/
PUBLIC.INTERVAL_OPERATION_UDF('date_col - intervalDayToSecond_col')
FROM PUBLIC.table_with_intervals;

-- Timestamp + Interval D to S
SELECT
/*** MSC-ERROR - MSCEWI3095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/
PUBLIC.INTERVAL_OPERATION_UDF('time_col + intervalDayToSecond_col')
FROM PUBLIC.table_with_intervals;

-- Timestamp - Interval Y to M
SELECT
/*** MSC-ERROR - MSCEWI3095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/
PUBLIC.INTERVAL_OPERATION_UDF('time_col - intervalYearToMonth_col')
FROM PUBLIC.table_with_intervals;

Recommendations

• Implement the UDF to simulate the Oracle behavior.

• Extract the already transformed value that was stored in the column during migration, and use it as a Snowflake Interval Constant when possible.

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