MSCEWI3095

Operation Between Interval Type and Date Type not Supported

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

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.

PreviousMSCEWI3094 NextMSCEWI3096

Last updated 1 year ago

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;

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;