INTERVAL YEAR TO MONTH Data Type

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

INTERVAL YEAR TO MONTH specify an interval data type to store a duration of time in years and months. (RedShift SQL Language Reference Interval data type)

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

Grammar Syntax

INTERVAL {YEAR | MONTH | YEAR TO MONTH}

The use of the Interval data type is planned for implementation in future updates.

Sample Source Patterns

Interval Year To Month in Create Table

Input:

Redshift

CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 INTERVAL YEAR,
	interval_year_col2 INTERVAL MONTH,
 	interval_year_col3 INTERVAL YEAR TO MONTH
);

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ( INTERVAL '12' YEAR);
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ( INTERVAL '5' MONTH);
INSERT INTO interval_year_to_month_table(interval_year_col3) VALUES ( INTERVAL '1000-11' YEAR TO MONTH );

Output

Snowflake

CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR DATA TYPE CONVERTED TO VARCHAR ***/!!!,
	interval_year_col2 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!!,
	interval_year_col3 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR TO MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"redshift"}}'
;

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ('12year, 0mons');

INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ('0year, 5mons');

INSERT INTO interval_year_to_month_table(interval_year_col3) VALUES ('1000year, 11mons');

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 Redshift 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

Input

Redshift

SELECT * FROM interval_year_to_month_table;

Result

Output

Snowflake

SELECT * FROM
interval_year_to_month_table;

Result

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.