SnowConvert AI - Redshift - Literals¶

Description ¶

A literal or constant is a fixed data value, composed of a sequence of characters or a numeric constant. (Redshift SQL Language reference Literals).

Amazon Redshift supports several types of literals, including:

Numeric literals for integer, decimal, and floating-point numbers.
Character literals, also referred to as strings, character strings, or character constants.
Datetime and interval literals, used with datetime data types.

Sample Source Patterns¶

Input Code:¶

Redshift¶

 -- Number literals.
SELECT 42 AS integer_literal, -- Simple integer
    -123 AS negative_integer, -- Negative integer
    3.14159 AS decimal_literal, -- Decimal number
    1E0 AS simple_float; -- Floating-point representation of 1

-- Character literals.   
SELECT 'Hello, World!' AS simple_string,
    'Line1\nLine2' AS newline_character, -- Interprets \n as literal
    'Tab\tCharacter' AS tab_character, -- Interprets \t as literal
    'The value is ' || 42 AS mixed_literal;

Result¶


integer_literal	negative_integer	decimal_literal	simple_float
42	-123	3.14159	1


simple_string	newline_character	tab_character	mixed_literal
42	Line1 Line2	Tab Character	The value is 42

Output Code:

Snowflake¶

 -- Number literals.
SELECT 42 AS integer_literal, -- Simple integer
    -123 AS negative_integer, -- Negative integer
    3.14159 AS decimal_literal, -- Decimal number
    1E0 AS simple_float; -- Floating-point representation of 1

-- Character literals.   
SELECT 'Hello, World!' AS simple_string,
    'Line1\nLine2' AS newline_character, -- Interprets \n as literal
    'Tab\tCharacter' AS tab_character, -- Interprets \t as literal
    'The value is ' || 42 AS mixed_literal;

Result¶


integer_literal	negative_integer	decimal_literal	simple_float
42	-123	3.14159	1


simple_string	newline_character	tab_character	mixed_literal
42	Line1 Line2	Tab Character	The value is 42

Known Issues¶

This functionality is not currently supported in Snowflake, but it will be supported through a future migration.

 select $MyTagForLiteral$
This is
a test
of a tag literal
$MyTagForLiteral$ as c1;

Date, time, and timestamp literals¶

Description ¶

Date, time, and timestamp literals supported by Amazon Redshift.(Redshift SQL Language reference Date, Time, Timestamp Literals).

Sample Source Patterns¶

Input Code:¶

Redshift¶

 --invalid
SELECT
DATEADD(month, 1, 'January 8, 1999'),
DATEADD(month, 1, '2000-Jan-31'),
DATEADD(month, 1, 'Jan-31-2000'),
DATEADD(month, 1, '20000215'),
DATEADD(month, 1, '080215'),
DATEADD(month, 1, '2008.366'),
DATEADD(month, 1, 'now');

--valid
SELECT
DATEADD(month, 1, '1999-01-08'),
DATEADD(month, 1, '1/8/1999'),
DATEADD(month, 1, '01/02/00'),
DATEADD(month, 1, '31-Jan-2000');

Output Code:

Snowflake¶

 --invalid
SELECT
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - 'January 8, 1999' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! 'January 8, 1999'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - '2000-Jan-31' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! '2000-Jan-31'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - 'Jan-31-2000' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! 'Jan-31-2000'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - '20000215' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! '20000215'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - '080215' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! '080215'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - '2008.366' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! '2008.366'),
 DATEADD(month, 1,
                   !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - 'now' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! 'now');

--valid
SELECT
 DATEADD(month, 1, '1999-01-08'),
 DATEADD(month, 1, '1/8/1999'),
 DATEADD(month, 1, '01/02/00'),
 DATEADD(month, 1, '31-Jan-2000');

Known Issues¶

Some DATE, TIME, and TIMESTAMP formats may produce different results in Redshift compared to Snowflake.

Related EWIs¶

SSC-EWI-RS0007: Date literal is not supported in Snowflake.

Interval Literals¶

Description ¶

Interval literals can be used in datetime calculations, such as, adding intervals to dates and timestamps, summing intervals, and subtracting an interval from a date or timestamp. Interval literals can be used as input values to interval data type columns in a table.. (Redshift SQL Language reference Interval Literals).

Warning

This grammar is partially supported in Snowflake.

Grammar Syntax ¶

 INTERVAL quoted-string [ year_to_month_qualifier ]
INTERVAL quoted-string [ day_to_second_qualifier ] [ (fractional_precision) ]

Snowflake Intervals can only be used in arithmetic operations. Intervals used in any other scenario are not supported.

The following formats are the only ones recognized and fully transformed by SnowConvert AI, allowing optional fields and most of the abbreviations without interval styles:

1 year 1 month 1 day 2 hour 3 minutes 4 seconds 123 ms
hh:mm:ss.ms
1 year 1 month 1 day hh:mm:ss.ms

Snowflake does not support literals with arithmetic signs. If the Literal contains an hour expression the expression can be partially transformed.

Sample Source Patterns¶

Supported scenarios¶

Input Code:¶

Redshift¶

 SELECT 
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1year 1month 1day 2hour 3 minute 4.1233455second' AS c1,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1year 1month 1day 2hour 3 minute 4.123second' AS c2,
'2024-01-01 00:00:00' ::TIMESTAMP +  INTERVAL '1.234567' AS c3,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' AS c4,
('2024-01-01 00:00:00'::timestamp without time zone + '1 day 02:03:04.123'::interval) AS c5,
('2024-01-01 00:00:00'::timestamp without time zone + '1 year 1 mon 00:00:01'::interval) AS c6,
('2024-01-01 00:00:00'::timestamp without time zone + '1 year 1 mon'::interval) AS c7,
('2024-01-01 00:00:00'::timestamp without time zone + '00:00:01.234567'::interval) AS c8,
('2024-01-01 00:00:00'::timestamp without time zone + '1 year 1 mon 1 day 02:03:04.123'::interval) AS c9,
('2024-01-01 00:00:00'::timestamp without time zone + '00:03:04.5678'::interval) AS c10,
('2024-01-01 00:00:00'::timestamp without time zone + '1 day 02:03:00'::interval) AS c11,
('2024-01-01 00:00:00'::timestamp without time zone + '3 days 01:59:00'::interval) AS c11,
('2024-01-01 00:00:00'::timestamp without time zone + '1 year 1 mon'::interval) AS c12,
('2024-01-01 00:00:00'::timestamp without time zone + '10 years'::interval) AS c13,
('2024-01-01 00:00:00'::timestamp without time zone + '1000 years'::interval) AS c14,
('2024-01-01 00:00:00'::timestamp without time zone + '100 years'::interval) AS c15,
('2024-01-01 00:00:00'::timestamp without time zone + '1 year 1 mon'::interval) AS c16
;

Output Code:¶

Snowflake¶

 SELECT
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1year, 1month, 1day, 2hour, 3 minute, 4 seconds, 123 ms' AS c1,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1year, 1month, 1day, 2hour, 3 minute, 4 seconds, 123 ms' AS c2,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 seconds, 234 ms' AS c3,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' AS c4,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 day, 02 hour, 03 minutes, 04 seconds, 123 ms') AS c5,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon, 00 hour, 00 minutes, 01 seconds') AS c6,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon') AS c7,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '00 hour, 00 minutes, 01 seconds, 234 ms') AS c8,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon, 1 day, 02 hour, 03 minutes, 04 seconds, 123 ms') AS c9,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '00 hour, 03 minutes, 04 seconds, 567 ms') AS c10,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 day, 02 hour, 03 minutes, 00 seconds') AS c11,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '3 days , 01 hour, 59 minutes, 00 seconds') AS c11,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon') AS c12,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '10 years') AS c13,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1000 years') AS c14,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '100 years') AS c15,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon') AS c16
;

Pending translation scenarios¶

Input Code:¶

Redshift¶

 SELECT
INTERVAL '1year 1month 1day 2hour 3 minute 4.1233455second',
'2024-01-01 00:00:00' ::TIMESTAMP +  INTERVAL '1.234567' SECOND AS c2,
'2024-01-01 00:00:00' ::TIMESTAMP +  INTERVAL '1.234567' SECOND (3) AS c3,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' YEAR AS c4,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' MONTH AS c5,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 2:3:4.5678' DAY TO MINUTE AS c6,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 2:3:4.5678' DAY TO SECOND AS c7,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 2:3' AS c8,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 49:59:0' AS c9,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 49:59:0' DAY AS c10,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 - 1 1'  AS c11,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1-1' AS c12,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 year -1 day' AS c13,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '3:4.5678' AS c14,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1-1 0 second 0 millisecond' AS c15,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 decade' AS c16,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 millenium' AS c17,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 century' AS c18,
('2024-01-01 00:00:00'::timestamp without time zone + ('00:00:01.234567')::interval second) AS c19,
('2024-01-01 00:00:00'::timestamp without time zone + ('00:00:01.235')::interval second (3)) AS c20,
('2024-01-01 00:00:00'::timestamp without time zone + ('1 year')::interval year) AS c21,
('2024-01-01 00:00:00'::timestamp without time zone + ('1 year 1 mon')::interval month) AS c22,
('2024-01-01 00:00:00'::timestamp without time zone + ('1 day 02:03:00')::interval day to minute) AS c23,
('2024-01-01 00:00:00'::timestamp without time zone + ('1 day 02:03:04.5678')::interval day to second) AS c24,
('2024-01-01 00:00:00'::timestamp without time zone + '-01:56:55.877'::interval) AS c25,
('2024-01-01 00:00:00'::timestamp without time zone + ('3 days')::interval day) AS c26;

Output Code:¶

Snowflake¶

 SELECT
INTERVAL '1year 1month 1day 2hour 3 minute 4.1233455second' !!!RESOLVE EWI!!! /*** SSC-EWI-0107 - INTERVAL LITERAL IS NOT SUPPORTED BY SNOWFLAKE IN THIS SCENARIO  ***/!!!,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 seconds, 234 ms' SECOND !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c2,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 seconds, 234 ms' SECOND (3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c3,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' YEAR !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c4,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '13 months' MONTH !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c5,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 , 2 hour, 3 minutes, 4 seconds, 567 ms' DAY TO MINUTE !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c6,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 , 2 hour, 3 minutes, 4 seconds, 567 ms' DAY TO SECOND !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c7,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 2:3' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c8,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 , 49 hour, 59 minutes, 0 seconds' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c9,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 , 49 hour, 59 minutes, 0 seconds' DAY !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c10,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 - 1 1' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!  AS c11,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1-1' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c12,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 year -1 day' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c13,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '3:4.5678' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c14,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1-1 0 second 0 millisecond' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c15,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 decade' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c16,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 millenium' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c17,
'2024-01-01 00:00:00' ::TIMESTAMP + INTERVAL '1 century' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!! AS c18,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '00 hour, 00 minutes, 01 seconds, 234 ms' second !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c19,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '00 hour, 00 minutes, 01 seconds, 235 ms' second (3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c20,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year' year !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c21,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 year, 1 mon' month !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c22,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 day, 02 hour, 03 minutes, 00 seconds' day to minute !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c23,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + INTERVAL '1 day, 02 hour, 03 minutes, 04 seconds, 567 ms' day to second !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c24,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + '-01:56:55.877':: VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c25,
('2024-01-01 00:00:00':: TIMESTAMP_NTZ + ('3 days'):: VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INTERVAL FORMAT' NODE ***/!!!) AS c26;

Known Issues¶

No issues were found.

Related EWIs¶

SSC-EWI-0107: Interval Literal Not Supported In Current Scenario.
SSC-EWI-0073: Pending Functional Equivalence Review.

NULLS¶

Description ¶

If a column in a row is missing, unknown, or not applicable, it is a null value or is said to contain null. (Redshift SQL Language reference Nulls Literals).

Nulls can appear in fields of any data type that are not restricted by primary key or NOT NULL constraints. A null is not equivalent to the value zero or to an empty string.

Sample Source Patterns¶

Input Code:¶

Redshift¶

 SELECT NULL IN (NULL, 0, 1, 2 ,3, 4);
SELECT 1 + NULL, 1 - NULL, 1 * NULL, 1 / NULL, 1 % NULL;

Result¶


Select1
NULL


1+NULL	1*NULL
NULL	NULL

Output Code:

Snowflake¶

 SELECT NULL IN (NULL, 0, 1, 2 ,3, 4);
SELECT 1 + NULL, 1 - NULL, 1 * NULL, 1 / NULL, 1 % NULL;

Result¶


Select1
NULL


1+NULL	1*NULL
NULL	NULL

Known Issues¶

No issues were found.

Related EWIs¶

There are no known issues.