SnowConvert AI - Redshift - Built-in functions¶

Note

For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.

Aggregate Functions¶

Aggregate functions compute a single result value from a set of input values. (Redshift SQL Language Reference Aggregate Functions (https://docs.aws.amazon.com/redshift/latest/dg/c_Aggregate_Functions.html)).

Redshift	Snowflake
ANY_VALUE ( [ DISTINCT \| ALL ] expression )	ANY_VALUE ( [ DISTINCT ]expression ) Notes: Snowflake supports the grammar, though ALL is disallowed. DISTINCT has no effect in either.
AVG (https://docs.aws.amazon.com/redshift/latest/dg/r_AVG.html) ( [ DISTINCT \| ALL ] expression )	AVG ( [ DISTINCT ] expression) Notes: Redshift and Snowflake may show different precision/decimals due to data type rounding/formatting.
COUNT (https://docs.aws.amazon.com/redshift/latest/dg/r_COUNT.html)	COUNT
LISTAGG (https://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html)	LISTAGG Notes: Redshift’s DISTINCT ignores trailing spaces (‘a ‘ = ‘a’); Snowflake’s does not. (See SSC-FDM-PG0013).
MAX (https://docs.aws.amazon.com/redshift/latest/dg/r_MAX.html)	MAX
MEDIAN (https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html)	MEDIAN Notes: Snowflake does not allow the use of date types, while Redshift does. (See SSC-FDM-PG0013).
MIN (https://docs.aws.amazon.com/redshift/latest/dg/r_MIN.html)	MIN
PERCENTILE_CONT (https://docs.aws.amazon.com/redshift/latest/dg/r_PERCENTILE_CONT.html)	PERCENTILE_CONT
STDDEV/STDDEV_SAMP ( [ DISTINCT \| ALL ] expression) STDDEV_POP ( [ DISTINCT \| ALL ] expression)	STDDEV/STDDEV_SAMP ( [ DISTINCT ] expression) STDDEV_POP ( [ DISTINCT ] expression) Notes: Snowflake supports all the grammar, though ALL is disallowed.
SUM (https://docs.aws.amazon.com/redshift/latest/dg/r_SUM.html)	SUM
VARIANCE/VAR_SAMP ( [ DISTINCT \| ALL ] expression) VAR_POP ( [ DISTINCT \| ALL ] expression)	VARIANCE/VAR_SAMP ( [ DISTINCT ] expression) VAR_POP ( [ DISTINCT ] expression) Notes: Snowflake supports all the grammar, though ALL is disallowed.

Array Functions¶

Creates an array of the SUPER data type. (Redshift SQL Language Reference Array Functions (https://docs.aws.amazon.com/redshift/latest/dg/c_Array_Functions.html)).

Redshift	Snowflake
ARRAY (https://docs.aws.amazon.com/redshift/latest/dg/r_array.html) ( [ expr1 ] [ , expr2 [ , … ] ] )	ARRAY_CONSTRUCT ( [ <expr1> ] [ , <expr2> [ , … ] ] )
ARRAY_CONCAT (https://docs.aws.amazon.com/redshift/latest/dg/r_array_concat.html) ( super_expr1, super_expr2 )	ARRAY_CAT ( <array1> , <array2> )
ARRAY_FLATTEN ( super_expr1,super_expr2,.. )	ARRAY_FLATTEN ( <array> ) Notes: the results may vary between platforms (See SSC-FDM-PG0013).
GET_ARRAY_LENGTH (https://docs.aws.amazon.com/redshift/latest/dg/get_array_length.html) ( super_expr )	ARRAY_SIZE ( <array> \| <variant>)
SPLIT_TO_ARRAY (https://docs.aws.amazon.com/redshift/latest/dg/split_to_array.html) ( string,delimiter )	SPLIT (<string>, <separator>) Notes: Redshift allows missing delimiters; Snowflake requires them, defaulting to comma
SUBARRAY (https://docs.aws.amazon.com/redshift/latest/dg/r_subarray.html) ( super_expr, start_position, length )	ARRAY_SLICE ( <array> , <from> , <to> ) Notes: Function names and the second argument differ; adjust arguments for equivalence.

Conditional expressions¶

Redshift	Snowflake
DECODE (https://docs.aws.amazon.com/redshift/latest/dg/r_DECODE_expression.html)	DECODE Notes: the results may vary between platforms (See SSC-FDM-PG0013).
COALESCE (https://docs.aws.amazon.com/redshift/latest/dg/r_NVL_function.html) ( expression, expression, … )	COALESCE ( expression, expression, … )
GREATEST (https://docs.aws.amazon.com/redshift/latest/dg/r_GREATEST_LEAST.html) ( value [, …] )	GREATEST_IGNORE_NULLS ( <expr1> [, <expr2> … ] )
LEAST (https://docs.aws.amazon.com/redshift/latest/dg/r_GREATEST_LEAST.html) ( value [, …] )	LEAST_IGNORE_NULLS ( <expr1> [, <expr2> … ])
NVL (https://docs.aws.amazon.com/redshift/latest/dg/r_NVL_function.html)( expression, expression, … )	NVL ( expression, expression ) Notes: Redshift’s NVL accepts multiple arguments; Snowflake’s NVL accepts only two. To match Redshift behavior, NVL with more than two arguments is converted to COALESCE.
NVL2 (https://docs.aws.amazon.com/redshift/latest/dg/r_NVL2.html)	NVL2
NULLIF (https://docs.aws.amazon.com/redshift/latest/dg/r_NULLIF_function.html)	NULLIF Notes: Redshift’s NULLIF ignores trailing spaces in some string comparisons, unlike Snowflake. Therefore, the transformation adds RTRIM for equivalence.

Data type formatting functions¶

Data type formatting functions provide an easy way to convert values from one data type to another. For each of these functions, the first argument is always the value to be formatted and the second argument contains the template for the new format. (Redshift SQL Language Reference Data type formatting functions (https://docs.aws.amazon.com/redshift/latest/dg/r_Data_type_formatting.html)).

Redshift Snowflake

Redshift	Snowflake
TO_CHAR (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_CHAR.html)	TO_CHAR Notes: Snowflake’s support for this function is partial (see SSC-EWI-0006).
TO_DATE (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_DATE_function.html)	TO_DATE Notes: Snowflake’s `TO_DATE` fails on invalid dates like ’20010631’ (June has 30 days), unlike Redshift’s lenient `TO_DATE`. Use `TRY_TO_DATE` in Snowflake to handle these cases by returning NULL. (see SSC-FDM-RS0004, SSC-EWI-0006, SSC-FDM-0032).

TO_CHAR (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_CHAR.html)

TO_CHAR

Notes: Snowflake’s support for this function is partial (see SSC-EWI-0006).

TO_DATE (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_DATE_function.html)

TO_DATE

Notes: Snowflake’s TO_DATE fails on invalid dates like ’20010631’ (June has 30 days), unlike Redshift’s lenient TO_DATE. Use TRY_TO_DATE in Snowflake to handle these cases by returning NULL. (see SSC-FDM-RS0004, SSC-EWI-0006, SSC-FDM-0032).

Date and time functions¶

Redshift	Snowflake
ADD_MONTHS (https://docs.aws.amazon.com/redshift/latest/dg/r_ADD_MONTHS.html)	ADD_MONTHS Notes: the results may vary between platforms (See SSC-FDM-PG0013).
AT TIME ZONE ‘timezone’ (https://docs.aws.amazon.com/redshift/latest/dg/r_AT_TIME_ZONE.html)	CONVERT_TIMEZONE ( <source_tz> , <target_tz> , <source_timestamp_ntz> ) CONVERT_TIMEZONE ( <target_tz> , <source_timestamp> ) Notes: Redshift defaults to UTC; the Snowflake function requires explicit UTC specification. Therefore, it will be added as the target timezone.
CONVERT_TIMEZONE (https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html)	CONVERT_TIMEZONE
CURRENT_DATE (https://docs.aws.amazon.com/redshift/latest/dg/r_CURRENT_DATE_function.html)	CURRENT_DATE()
DATE (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_DATE_function.html)	DATE
DATEADD/DATE_ADD (https://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html) ( datepart, interval, {date \| time \| timetz \| timestamp} )	DATE_ADD ( <date_or_time_part>, <value>, <date_or_time_expr> ) Notes: Invalid date part formats are translated to Snowflake-compatible formats.
DATEDIFF/DATE_DIFF (https://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html)	DATEDIFF Notes: Invalid date part formats are translated to Snowflake-compatible formats.
DATE_PART/PGDATE_PART (https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html)	DATE_PART Notes: this function is partially supported by Snowflake. (See SSC-EWI-0006).
DATE_PART_YEAR (https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_YEAR.html) (date)	YEAR ( <date_or_timestamp_expr> ) Notes: the results may vary between platforms (See SSC-FDM-PG0013).
DATE_TRUNC (https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_TRUNC.html)	DATE_TRUNC Notes: Invalid date part formats are translated to Snowflake-compatible formats.
GETDATE (https://docs.aws.amazon.com/redshift/latest/dg/r_GETDATE.html)()	GETDATE()
LAST_DAY (https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html)	LAST_DAY Notes: the results may vary between platforms (See SSC-FDM-PG0013).
NEXT_DAY (https://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html)	NEXT_DAY Notes: the results may vary between platforms (See SSC-FDM-PG0013).
SYSDATE (https://docs.aws.amazon.com/redshift/latest/dg/r_SYSDATE.html)	SYSDATE()
TIMESTAMP (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_TIMESTAMP.html)	TO_TIMESTAMP
TRUNC (https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNC_date.html)	TRUNC (https://docs.snowflakhttps/docs.snowflake.com/en/sql-reference/functions/trunc2e.com/en/sql-reference/functions/trunc2)
EXTRACT (https://docs.aws.amazon.com/redshift/latest/dg/r_EXTRACT_function.html)	EXTRACT Notes: Part-time or Date time supported: DAY, DOW, DOY, EPOCH, HOUR, MINUTE, MONTH, QUARTER, SECOND, WEEK, YEAR.

Note

Redshift timestamps default to microsecond precision (6 digits); Snowflake defaults to nanosecond precision (9 digits). Adjust precision as needed using ALTER SESSION (for example, ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';). Precision loss may occur depending on the data type used.

Since some formats are incompatible with Snowflake, adjusting the account parameters DATE_INPUT_FORMAT or TIME_INPUT_FORMAT might maintain functional equivalence between platforms.

Hash Functions¶

A hash function is a mathematical function that converts a numerical input value into another value. (Redshift SQL Language Reference Hash functions (https://docs.aws.amazon.com/redshift/latest/dg/hash-functions.html)).

Redshift	Snowflake
FNV_HASH (https://docs.aws.amazon.com/redshift/latest/dg/r_FNV_HASH.html) (value [, seed])	HASH ( <expr> [ , <expr> … ] \| )* Notes: In Redshift’s FNV_HASH function, the seed parameter is optional, but it is not used in the Snowflake’s Hash function since the seed parameter is only used on FNV algorithm.

Redshift

Snowflake

FNV_HASH (https://docs.aws.amazon.com/redshift/latest/dg/r_FNV_HASH.html) (value [, seed])

HASH ( <expr> [ , <expr> … ] | *)

Notes: In Redshift’s FNV_HASH function, the seed parameter is optional, but it is not used in the Snowflake’s Hash function since the seed parameter is only used on FNV algorithm.

JSON Functions¶

Redshift	Snowflake
JSON_EXTRACT_PATH_TEXT (https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html)	JSON_EXTRACT_PATH_TEXT Notes: Redshift treats newline, tab, and carriage return characters literally; Snowflake interprets them. A JSON literal and dot-separated path are required to access nested objects in the Snowflake function. Paths with spaces in variables must be quoted.

Redshift

Snowflake

JSON_EXTRACT_PATH_TEXT (https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html)

JSON_EXTRACT_PATH_TEXT

Notes:

Redshift treats newline, tab, and carriage return characters literally; Snowflake interprets them.
A JSON literal and dot-separated path are required to access nested objects in the Snowflake function.
Paths with spaces in variables must be quoted.

Math functions¶

Redshift	Snowflake
ACOS (https://docs.aws.amazon.com/redshift/latest/dg/r_ACOS.html)	ACOS
ASIN (https://docs.aws.amazon.com/redshift/latest/dg/r_ASIN.html)	ASIN
ATAN (https://docs.aws.amazon.com/redshift/latest/dg/r_ATAN.html)	ATAN
ATAN2 (https://docs.aws.amazon.com/redshift/latest/dg/r_ATAN2.html)	ATAN2
CBRT (https://docs.aws.amazon.com/redshift/latest/dg/r_CBRT.html)	CBRT
CEIL/CEILING (https://docs.aws.amazon.com/redshift/latest/dg/r_CEILING_FLOOR.html)	CEIL
COS (https://docs.aws.amazon.com/redshift/latest/dg/r_COS.html)	COS
COT (https://docs.aws.amazon.com/redshift/latest/dg/r_COT.html)	COT
DEGREES (https://docs.aws.amazon.com/redshift/latest/dg/r_DEGREES.html)	DEGREES
DEXP (https://docs.aws.amazon.com/redshift/latest/dg/r_DEXP.html)	EXP
DLOG1/LN (https://docs.aws.amazon.com/redshift/latest/dg/r_DLOG1.html)	LN
DLOG10 (https://docs.aws.amazon.com/redshift/latest/dg/r_DLOG10.html) (number)	LOG (10, number)
EXP (https://docs.aws.amazon.com/redshift/latest/dg/r_EXP.html)	EXP
FLOOR (https://docs.aws.amazon.com/redshift/latest/dg/r_FLOOR.html)	FLOOR
LOG (https://docs.aws.amazon.com/redshift/latest/dg/r_LOG.html)	LOG
MOD (https://docs.aws.amazon.com/redshift/latest/dg/r_MOD.html)	MOD
PI (https://docs.aws.amazon.com/redshift/latest/dg/r_PI.html)	PI
POWER/POW (https://docs.aws.amazon.com/redshift/latest/dg/r_POWER.html)	POWER/POW
RADIANS (https://docs.aws.amazon.com/redshift/latest/dg/r_RADIANS.html)	RADIANS
RANDOM (https://docs.aws.amazon.com/redshift/latest/dg/r_RANDOM.html)	RANDOM
ROUND (https://docs.aws.amazon.com/redshift/latest/dg/r_ROUND.html)	ROUND
SIN (https://docs.aws.amazon.com/redshift/latest/dg/r_SIN.html)	SIN
SIGN (https://docs.aws.amazon.com/redshift/latest/dg/r_SIGN.html)	SIGN
SQRT (https://docs.aws.amazon.com/redshift/latest/dg/r_SQRT.html)	SQRT
TAN (https://docs.aws.amazon.com/redshift/latest/dg/r_TAN.html)	TAN
TRUNC (https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNC.html)	TRUNC

Note

Redshift and Snowflake results may differ in scale.

String functions¶

String functions process and manipulate character strings or expressions that evaluate to character strings. (Redshift SQL Language Reference String functions (https://docs.aws.amazon.com/redshift/latest/dg/String_functions_header.html)).

Redshift	Snowflake
ASCII (https://docs.aws.amazon.com/redshift/latest/dg/r_ASCII.html)	ASCII
BTRIM (https://docs.aws.amazon.com/redshift/latest/dg/r_BTRIM.html)	TRIM
CHAR_LENGTH (https://docs.aws.amazon.com/redshift/latest/dg/r_CHAR_LENGTH.html)	LENGTH
CHARACTER_LENGTH (https://docs.aws.amazon.com/redshift/latest/dg/r_CHARACTER_LENGTH.html)	LENGTH
CHARINDEX (https://docs.aws.amazon.com/redshift/latest/dg/r_CHARINDEX.html)	CHARINDEX
CHR (https://docs.aws.amazon.com/redshift/latest/dg/r_CHR.html)	CHR
CONCAT (https://docs.aws.amazon.com/redshift/latest/dg/r_CONCAT.html)	CONCAT
INITCAP (https://docs.aws.amazon.com/redshift/latest/dg/r_INITCAP.html)	INITCAP
LEFT/RIGHT	LEFT/RIGHT Notes: For negative lengths in `LEFT`/`RIGHT`, Snowflake returns an empty string; Redshift raises an error.
LEN (https://docs.aws.amazon.com/redshift/latest/dg/r_LEN.html)	LEN
LOWER (https://docs.aws.amazon.com/redshift/latest/dg/r_LOWER.html)	LOWER
OCTET_LENGTH (https://docs.aws.amazon.com/redshift/latest/dg/r_OCTET_LENGTH.html)	OCTET_LENGTH Notes: the results may vary between platforms (See SSC-FDM-PG0013).
QUOTE_IDENT (https://docs.aws.amazon.com/redshift/latest/dg/r_QUOTE_IDENT.html) (string)	CONCAT (‘”’, string, ‘”’)
REGEXP_REPLACE (https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html)	REGEXP_REPLACE Notes: This function includes a `parameters` argument that enables the user to interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect, represented by the `p` value, this is removed to avoid any issues. (See SSC-EWI-0009, SC-FDM-0032, SSC-FDM-PG0011).
REPEAT (https://docs.aws.amazon.com/redshift/latest/dg/r_REPEAT.html)	REPEAT
REPLACE (https://docs.aws.amazon.com/redshift/latest/dg/r_REPLACE.html)	REPLACE
REPLICATE (https://docs.aws.amazon.com/redshift/latest/dg/r_REPLICATE.html)	REPEAT
REVERSE (https://docs.aws.amazon.com/redshift/latest/dg/r_REVERSE.html)	REVERSE
SOUNDEX (https://docs.aws.amazon.com/redshift/latest/dg/SOUNDEX.html)	SOUNDEX Notes: Certain special characters, the results may vary between platforms (See SSC-FDM-PG0013).
SPLIT_PART (https://docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html)	SPLIT_PART Notes: Snowflake and Redshift handle SPLIT_PART differently with case-insensitive collations.
STRPOS (https://docs.aws.amazon.com/redshift/latest/dg/r_STRPOS.html) (string, substring )	POSITION ( <expr1> IN <expr> )
SUBSTRING (https://docs.aws.amazon.com/redshift/latest/dg/r_SUBSTRING.html)	SUBSTRING Notes: Snowflake partially supports this function. Redshift’s `SUBSTRING`, with a non-positive `start_position`, calculates `start_position + number_characters` (returning ‘’ if the result is non-positive). Snowflake’s behavior differs. (See SSC-EWI-RS0006).
TEXTLEN (https://docs.aws.amazon.com/redshift/latest/dg/r_TEXTLEN.html)	LENGTH
TRANSLATE (https://docs.aws.amazon.com/redshift/latest/dg/r_TRANSLATE.html)	TRANSLATE
TRIM (https://docs.aws.amazon.com/redshift/latest/dg/r_TRIM.html)	TRIM Notes: Redshift uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.
UPPER (https://docs.aws.amazon.com/redshift/latest/dg/r_UPPER.html)	UPPER

SUPER type information functions¶

Redshift	Snowflake
IS_ARRAY (https://docs.aws.amazon.com/redshift/latest/dg/r_is_array.html)	IS_ARRAY Notes: the results may vary between platforms (See SSC-FDM-PG0013).
IS_BOOLEAN (https://docs.aws.amazon.com/redshift/latest/dg/r_is_boolean.html)	IS_BOOLEAN Notes: the results may vary between platforms (See SSC-FDM-PG0013).

Redshift

Snowflake

IS_ARRAY (https://docs.aws.amazon.com/redshift/latest/dg/r_is_array.html)

IS_ARRAY

Notes: the results may vary between platforms (See SSC-FDM-PG0013).

IS_BOOLEAN (https://docs.aws.amazon.com/redshift/latest/dg/r_is_boolean.html)

IS_BOOLEAN

Notes: the results may vary between platforms (See SSC-FDM-PG0013).

Window functions¶

Redshift	Snowflake
AVG (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_AVG.html)	AVG Notes: AVG rounding/formatting can vary by data type between Redshift and Snowflake.
COUNT (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_COUNT.html)	COUNT
DENSE_RANK (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_DENSE_RANK.html)	DENSE_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
FIRST_VALUE (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html)	FIRST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get `ORDER BY <expr>.`
LAG (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html)	LAG
LAST_VALUE (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_last_value.html)	LAST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get `ORDER BY <expr>`.
LEAD (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LEAD.html)	LEAD Notes: Redshift allows constant or expression offsets; Snowflake allows only constant offsets.
LISTAGG (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LISTAGG.html)	LISTAGG Notes: Redshift’s DISTINCT ignores trailing spaces (‘a ‘ = ‘a’); Snowflake’s does not. (See SSC-FDM-PG0013).
MEDIAN (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_MEDIAN.html)	MEDIAN Notes: Snowflake does not allow the use of date types, while Redshift does. (See SSC-FDM-PG0013).
NTH_VALUE (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_NTH.html)	NTH_VALUE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
NTILE (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_NTILE.html)	NTILE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`. (See SSC-FDM-PG0013).
PERCENT_RANK (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_PERCENT_RANK.html)	PERCENT_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
PERCENTILE_CONT (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_PERCENTILE_CONT.html)	PERCENTILE_CONT Notes: Rounding varies between platforms.
PERCENTILE_DISC (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_PERCENTILE_DISC.html)	PERCENTILE_DISC
RANK (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_RANK.html)	RANK
RATIO_TO_REPORT (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_RATIO_TO_REPORT.html)	RATIO_TO_REPORT Notes: the results may vary between platforms (See SSC-FDM-PG0013).
ROW_NUMBER (https://docs.aws.amazon.com/redshift/latest/dg/r_WF_ROW_NUMBER.html)	ROW_NUMBER Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
STDDEV_SAMP \| STDDEV \| STDDEV_POP ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )	STDDEV/STDDEV_SAMP /STDDEV_POP ( [ DISTINCT ] expr1) OVER ( [ PARTITION BY expr2 ] [ ORDER BY expr3 [ ASC \| DESC ] [ <window_frame> ] ] ) Notes: Snowflake supports all the grammar, though ALL is disallowed.
VAR_SAMP \| VARIANCE \| VAR_POP ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )	VARIANCE/VAR_SAMP / VAR_POP ( [ DISTINCT ] expr1) OVER ( [ PARTITION BY expr2 ] [ ORDER BY expr3 [ ASC \| DESC ] [ <window_frame> ] ] ) Notes: Snowflake supports all the grammar, though ALL is disallowed.

Known Issues ¶

For more information, see Quoted identifiers in functions.

SSC-EWI-0006: Date or time format is not supported in Snowflake.
SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied
SSC-FDM-RS0004: Invalid dates will cause errors in Snowflake.
SSC-FDM-PG0013: Function syntactically supported by Snowflake but may have functional differences.

IDENTITY¶

Description ¶

The IDENTITY function is a system function that operates on a specified column of a table to determine the initial value for the identity. If the initial value is not available, it defaults to the value provided in the function. This will be translation to a Sequence in Snowflake.

Grammar Syntax ¶

 "identity"(oid_id, oid_table_id, default)

Note

This function is no longer supported in Redshift. It uses the default value to define the identity and behaves like a standard identity column.

Sample Source Patterns¶

Input Code:¶

Redshift¶

 CREATE TABLE IF NOT EXISTS table_test
(
    id integer,
    inventory_combo BIGINT  DEFAULT "identity"(850178, 0, '5,3'::text)
);

INSERT INTO table_test (id) VALUES
    (1),
    (2),
    (3),
    (4);

SELECT * FROM table_test;

Results¶

id	inventory_combo
1	5
2	8
3	11
3	14

Output Code:

Snowflake¶

 CREATE TABLE IF NOT EXISTS table_test
(
    id integer,
    inventory_combo BIGINT IDENTITY(5,3) ORDER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/13/2024",  "domain": "test" }}';

INSERT INTO table_test (id) VALUES
    (1),
    (2),
    (3),
    (4);

SELECT * FROM
    table_test;

Results¶

id	inventory_combo
1	5
2	8
3	11
3	14

There are no known issues.

TO_CHAR¶

Date function

Description¶

TO_CHAR converts a timestamp or numeric expression to a character-string data format. (Redshift SQL Language Reference TO_CHAR function (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_CHAR.html))

Warning

This function is partially supported in Snowflake.

For more information, see Quoted identifiers in functions.

Grammar Syntax¶

 TO_CHAR(timestamp_expression | numeric_expression , 'format')

Sample Source Patterns¶

Input Code:¶

Redshift¶

 SELECT TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYYY'),
       TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYY'),
       TO_CHAR(timestamp '2009-12-31 23:15:59', 'TH'),
       "to_char"(timestamp '2009-12-31 23:15:59', 'MON-DY-DD-YYYY HH12:MIPM'),
       TO_CHAR(125.8, '999.99'),
       "to_char"(125.8, '999.99');

Results¶

TO_CHAR	TO_CHAR	TO_CHAR	TO_CHAR	TO_CHAR
2009	009	DEC-THU-31-2009 11:15PM	125.80	125.80

Output Code:¶

Snowflake¶

 SELECT
       TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYYY'),
       PUBLIC.YEAR_PART_UDF(timestamp '2009-12-31 23:15:59', 3),
       TO_CHAR(timestamp '2009-12-31 23:15:59', 'TH') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - TH FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
       PUBLIC.MONTH_SHORT_UDF(timestamp '2009-12-31 23:15:59', 'uppercase') || '-' || PUBLIC.DAYNAME_SHORT_UDF(timestamp '2009-12-31 23:15:59', 'uppercase') || TO_CHAR(timestamp '2009-12-31 23:15:59', '-DD-YYYY HH12:MI') || PUBLIC.MERIDIAN_INDICATORS_UDF(timestamp '2009-12-31 23:15:59', 'uppercase'),
       TO_CHAR(125.8, '999.99'),
       TO_CHAR(125.8, '999.99');

Results¶

TO_CHAR	TO_CHAR
2009	Dec-Thu-31-2009 11:15PM

Known Issues ¶

No issues were found.

SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.

For datetime values¶

Translation specification for the TO_CHAR function when transforming date or timestamp values to string

Description¶

The following format strings apply to functions such as TO_CHAR. These strings can contain datetime separators (such as ‘-’, ‘/’, or ‘:’) and the following “dateparts” and “timeparts”. (Redshift Datetime format strings reference page (https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html))

Grammar Syntax¶

TO_CHAR (timestamp_expression, 'format')

The following table specifies the mapping of each format element to Snowflake:

Redshift	Snowflake
`BC, AD, bc, ad` (upper and lowercase era indicators)	`PUBLIC.ERA_INDICATORS_UDF`
`B.C,. A.D., b.c., a.d.` (upper and lowercase era indicators with points)	`PUBLIC.ERA_INDICATORS_WITH_POINTS_UDF`
`CC`	`PUBLIC.CENTURY_UDF`
`YYYY` and `YY`	Directly supported
`YYY` and `Y`	`PUBLIC.YEAR_PART_UDF`
`Y,YYY`	`PUBLIC.YEAR_WITH_COMMA_UDF`
`IYYY`	`YEAROFWEEKISO`
`I, IY, IYY`	`PUBLIC.ISO_YEAR_PART_UDF`
`Q`	`QUARTER`
`MONTH, Month, month`	`PUBLIC.FULL_MONTH_NAME_UDF`
`MON, Mon, mon`	`PUBLIC.MONTH_SHORT_UDF`
`RM, rm`	`PUBLIC.ROMAN_NUMERALS_MONTH_UDF`
`W`	`PUBLIC.WEEK_OF_MONTH_UDF`
`WW`	`PUBLIC.WEEK_NUMBER_UDF`
`IW`	`WEEKISO`
`DAY, Day, day`	`PUBLIC.DAYNAME_LONG_UDF`
`DY, Dy, dy`	`PUBLIC.DAYNAME_SHORT_UDF`
`DDD`	`DAYOFYEAR`
`IDDD`	`PUBLIC.DAY_OF_YEAR_ISO_UDF`
`D`	`PUBLIC.DAY_OF_WEEK_UDF` Notes: For this UDF to work correctly the Snowflake session parameter `WEEK_START` should have its default value (`0`).
`ID`	`DAYOFWEEKISO`
`J`	`PUBLIC.JULIAN_DAY_UDF`
`HH24`	Directly supported
`HH`	`HH12`
`HH12`	Directly supported
`MI`	Directly supported
`SS`	Directly supported
`MS`	`FF3`
`US`	`FF6`
`AM, PM, am, pm` (upper and lowercase meridian indicators)	`PUBLIC.MERIDIAN_INDICATORS_UDF`
`A.M., P.M., a.m., p.m.` (upper and lowercase meridian indicators with points)	`PUBLIC.MERIDIAN_INDICATORS_WITH_POINTS_UDF`
`TZ` and `tz`	`UTC` and `utc` Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result.
`OF`	+00 Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result.
`SSSS`	`PUBLIC.SECONDS_PAST_MIDNIGHT`
`SP`	Notes: This is a PostgreSQL template pattern modifier for “spell mode”, however it does nothing on Redshift, so it is removed from the output.
`FX`	Notes: This is another template pattern modifier for “fixed format”, however it has no use on the TO_CHAR function so it is removed.

Sample Source Patterns¶

Direct format elements transformation (no functions/UDFs)¶

The result is preserved as a single TO_CHAR function

Redshift¶

Query¶

 SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH:MI:SS.MS') AS col1;

Result¶

+----------------------+
|col1                  |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+

Snowflake¶

Query¶

 SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH12:MI:SS.FF3') AS col1;

Result¶

+----------------------+
|col1                  |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+

Format transformation using functions/UDFs¶

The result is a concatenation of multiple TO_CHAR, UDFs and Snowflake built-in functions that generate the equivalent string representation of the datetime value

Redshift¶

Query¶

 SELECT TO_CHAR(DATE '2025-07-05', '"Today is " Month DAY DD, "it belongs to the week " IW') AS result;

Result¶

+-------------------------------------------------------------+
|result                                                       |
+-------------------------------------------------------------+
|Today is  July      SATURDAY  05, it belongs to the week  27 |
+-------------------------------------------------------------+

Snowflake¶

Query¶

 SELECT
    'Today is ' ||
    TO_CHAR(DATE '2025-07-05', ' ') ||
    PUBLIC.FULL_MONTH_NAME_UDF(DATE '2025-07-05', 'firstOnly') ||
    ' ' ||
    PUBLIC.DAYNAME_LONG_UDF(DATE '2025-07-05', 'uppercase') ||
    TO_CHAR(DATE '2025-07-05', ' DD, ') ||
    'it belongs to the week ' ||
    TO_CHAR(DATE '2025-07-05', ' ') ||
    WEEKISO(DATE '2025-07-05') AS result;

Result¶

+-------------------------------------------------------------+
|result                                                       |
+-------------------------------------------------------------+
|Today is  July      SATURDAY  05, it belongs to the week  27 |
+-------------------------------------------------------------+

Quoted text¶

Format elements in double quoted text are added to the output directly without interpreting them, escaped double quotes are transformed to their Snowflake escaped equivalent.

Redshift¶

Query¶

 SELECT
    TO_CHAR(DATE '2025-01-16', 'MM "TESTING DD" DD') AS result1,
    TO_CHAR(DATE '2025-01-16', 'MM TESTING \\"DD\\" DD') AS result2,
    TO_CHAR(DATE '2025-01-16', 'MM "TESTING \\"DD\\"" DD') AS result3;

Result¶

+-----------------+-------------------+-------------------+
|result1          |result2            |result3            |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+

Snowflake¶

Query¶

 SELECT
    TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING DD' || TO_CHAR(DATE '2025-01-16', ' DD') AS result1,
    TO_CHAR(DATE '2025-01-16', 'MM TEST') || PUBLIC.ISO_YEAR_PART_UDF(DATE '2025-01-16', 1) || TO_CHAR(DATE '2025-01-16', 'NG ""DD"" DD') AS result2,
    TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING "DD"' || TO_CHAR(DATE '2025-01-16', ' DD') AS result3;

Result¶

+-----------------+-------------------+-------------------+
|result1          |result2            |result3            |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+

Known Issues¶

Template pattern modifiers not supported¶

The following format template modifiers:

FM (fill mode)
TH and th (uppercase and lowercase ordinal number suffix)
TM (translation mode)

Are not supported, including them in a format will generate SSC-EWI-0006

Input code:

 SELECT TO_CHAR(CURRENT_DATE, 'FMMonth'),
TO_CHAR(CURRENT_DATE, 'DDTH'),
TO_CHAR(CURRENT_DATE, 'DDth'),
TO_CHAR(CURRENT_DATE, 'TMMonth');

Output code:

 SELECT
TO_CHAR(CURRENT_DATE(), 'FM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - FMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDTH') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - DDTH FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDth') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - DDth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'TM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - TMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!;

Format parameter passed through variable

When the format parameter is passed as a variable instead of a string literal, the transformation of format elements can not be applied, an FDM will be added to the uses of the function warning about it.

Input code:

 SELECT TO_CHAR(d, 'YYYY/MM/DD'),
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);

Output code:

 SELECT TO_CHAR(d, 'YYYY/MM/DD'),
--** SSC-FDM-0032 - PARAMETER 'format_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);

SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied

SnowConvert AI - Redshift - Built-in functions¶

Aggregate Functions¶

Array Functions¶

Conditional expressions¶

Data type formatting functions¶

Date and time functions¶

Hash Functions¶

JSON Functions¶

Math functions¶

String functions¶

SUPER type information functions¶

Window functions¶

Known Issues ¶

Related EWIs¶

IDENTITY¶

Description ¶

Grammar Syntax ¶

Sample Source Patterns¶

Input Code:¶

Redshift¶

Results¶

Snowflake¶

Results¶

Related EWIs¶

TO_CHAR¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Input Code:¶

Redshift¶

Results¶

Output Code:¶

Snowflake¶

Results¶

Known Issues ¶

Related EWIs¶

For datetime values¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Direct format elements transformation (no functions/UDFs)¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

Format transformation using functions/UDFs¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

Quoted text¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

Known Issues¶

Template pattern modifiers not supported¶

Related EWIs¶