INTERVAL YEAR TO MONTH Data Type

Description

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. There is no equivalent in Snowflake so it is transformed to Varchar (Oracle SQL Language Reference INTERVAL YEAR TO MONTH Data Type)

There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.

INTERVAL YEAR [(year_precision)] TO MONTH

Sample Source Patterns

Interval Year To Month in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 interval year to month,
	interval_year_col2 interval year(4) to month
);

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ( INTERVAL '1-2' YEAR TO MONTH ); 
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ( INTERVAL '1000-11' YEAR(4) TO MONTH );

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE interval_year_to_month_table
	(
		interval_year_col1 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year to month DATA TYPE CONVERTED TO VARCHAR ***/!!!,
		interval_year_col2 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year(4) to month DATA TYPE CONVERTED TO VARCHAR ***/!!!
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ('1y, 2mm');

	INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ('1000y, 11mm');

The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. In order to use the value, it needs to be extracted and used as an Interval constant (if possible).

Original Oracle value: INTERVAL '1-2' YEAR TO MONTH

Value stored in Snowflake column: '1y, 2m'

Value as Snowflake Interval constant: INTERVAL '1y, 2m'

Retrieving data from an Interval Year To Month column

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM interval_year_to_month_table;

Result

|INTERVAL_YEAR_COL1|INTERVAL_YEAR_COL2|
|------------------|------------------|
|1-2               |                  |
|                  |1000-11           |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
interval_year_to_month_table;

Result

INTERVAL_YEAR_COL1|INTERVAL_YEAR_COL2|
------------------+------------------+
1y, 2m            |                  |
                  |1000y, 11m        |

Known Issues

1. Only arithmetic operations are supported

Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported.

Related EWIs

• SSC-EWI-0036: Data type converted to another data type.