SnowConvert AI - Redshift - Built-in functions¶

备注

For more information about built-in functions and their Snowflake equivalents, also see Common built-in 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 )
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 (https://docs.aws.amazon.com/redshift/latest/dg/r_STDDEV_functions.html) ( [ DISTINCT	ALL ] expression) STDDEV_POP (https://docs.aws.amazon.com/redshift/latest/dg/r_STDDEV_functions.html) ( [ DISTINCT
SUM (https://docs.aws.amazon.com/redshift/latest/dg/r_SUM.html)	SUM
VARIANCE/VAR_SAMP (https://docs.aws.amazon.com/redshift/latest/dg/r_VARIANCE_functions.html) ( [ DISTINCT	ALL ] expression) VAR_POP (https://docs.aws.amazon.com/redshift/latest/dg/r_VARIANCE_functions.html) ( [ DISTINCT

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 (https://docs.aws.amazon.com/redshift/latest/dg/array_flatten.html) ( 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
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).

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).

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-OOO6).
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.

备注

Redshift timestamps default to microsecond precision (6 digits); Snowflake defaults to nanosecond precision (9 digits). Adjust precision as needed using ALTER SESSION (e.g., 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.

哈希函数¶

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> ... ]

Redshift

Snowflake

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

HASH ( <expr> [ , <expr> ... ]

JSON 函数¶

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

备注

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
VAR_SAMP	VARIANCE

已知问题 ¶

For more information about quoted identifiers in functions, click here.

IDENTITY¶

描述 ¶

IDENTITY 函数是一个系统函数，它对表中指定的列进行操作，以确定标识的初始值。如果初始值不可用，则默认使用函数中提供的值。这将转换为 Snowflake 中的 Sequence。

语法 ¶

 "identity"(oid_id, oid_table_id, default)

Copy

备注

该函数在 Redshift 中已不再受支持。它使用默认值来定义标识，其行为类似于标准的标识列。

示例源模式¶

输入代码：¶

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;

Copy

Results¶

id	inventory_combo
1	5
2	8
3	11
3	14

输出代码：

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;

Copy

Results¶

id	inventory_combo
1	5
2	8
3	11
3	14

TO_CHAR¶

Date function

描述¶

TO_CHAR 将时间戳或数字表达式转换为字符串数据格式。（Redshift SQL 语言参考 TO_CHAR 函数 (https://docs.aws.amazon.com/redshift/latest/dg/r_TO_CHAR.html)）

警告

该函数在 Snowflake 中部分受支持。

有关函数中带引号标识符的更多信息，点击此处。

语法¶

 TO_CHAR(timestamp_expression | numeric_expression , 'format')

Copy

示例源模式¶

输入代码：¶

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');

Copy

Results¶

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

输出代码：¶

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');

Copy

Results¶

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

已知问题 ¶

未发现任何问题。

对于日期时间值¶

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

描述¶

以下格式字符串适用于 TO_CHAR 等函数。这些字符串可以包含日期时间分隔符（如“-”、“/”或“:”）以及下列“日期部分”和“时间部分”。（Redshift 日期时间格式字符串参考页面 (https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html)）

语法¶

TO_CHAR (timestamp_expression, 'format')

Copy

下表指定了每个格式元素到 Snowflake 的映射：

Redshift	Snowflake
`BC, AD, bc, ad`（不区分大小写的纪元指示符）	`PUBLIC.ERA_INDICATORS_UDF`
`B.C、A.D., b.c., a.d.`（带点的大小写纪元指示符）	`PUBLIC.ERA_INDICATORS_WITH_POINTS_UDF`
`CC`	`PUBLIC.CENTURY_UDF`
`YYYY` 和 `YY`	直接支持
`YYY` 和 `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`	直接支持
`HH`	`HH12`
`HH12`	直接支持
`MI`	直接支持
`SS`	直接支持
`MS`	`FF3`
`US`	`FF6`
`AM, PM, am, pm`（不区分大小写的上午/下午指示符）	`PUBLIC.MERIDIAN_INDICATORS_UDF`
`A.M., P.M., a.m., p.m.`（带点的大小写上午/下午指示符）	`PUBLIC.MERIDIAN_INDICATORS_WITH_POINTS_UDF`
`TZ` 和 `tz`	`UTC` and `utc` Notes: According to the redshift documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timestamptz), 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 (https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timestamptz), 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.

示例源模式¶

直接格式元素转换（无函数/UDFs）¶

结果保留为单个 TO_CHAR 函数

Redshift¶

Query¶

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

Copy

Result¶

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

Copy

Snowflake¶

Query¶

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

Copy

Result¶

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

Copy

使用函数 /UDFs 进行格式转换¶

结果是由多个 TO_CHAR、UDFs 以及 Snowflake 内置函数拼接而成，用来生成日期时间值的等效字符串表示。

Redshift¶

Query¶

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

Copy

Result¶

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

Copy

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;

Copy

Result¶

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

Copy

引号文本¶

双引号文本中的格式元素无需解释即可直接添加到输出中，转义的双引号将转换为 Snowflake 转义等效的格式。

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;

Copy

Result¶

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

Copy

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;

Copy

Result¶

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

Copy

已知问题¶

不支持模板模式修饰符¶

以下格式模板修饰符：

FM（填充模式）
TH 和 th（大写和小写的序数后缀）
TM（翻译模式）

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

输入代码：

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

Copy

输出代码：

 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. ***/!!!;

Copy

通过变量传递的格式参数

当格式参数作为变量而不是字符串字面量传递时，将无法应用格式元素的转换，并将添加一个 FDM 到函数的使用中，用以发出相关警告。

输入代码：

 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);

Copy

输出代码：

 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);

Copy

SnowConvert AI - Redshift - Built-in functions¶

聚合函数¶

Array Functions¶

Conditional expressions¶

Data type formatting functions¶

Date and time functions¶

哈希函数¶

JSON 函数¶

Math functions¶

String functions¶

SUPER type information functions¶

Window functions¶

已知问题 ¶

相关的 EWIs¶

IDENTITY¶

描述 ¶

语法 ¶

示例源模式¶

输入代码：¶

Redshift¶

Results¶

Snowflake¶

Results¶

相关的 EWIs¶

TO_CHAR¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

已知问题 ¶

相关的 EWIs¶

对于日期时间值¶

描述¶

语法¶

示例源模式¶

直接格式元素转换（无函数/UDFs）¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

使用函数 /UDFs 进行格式转换¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

引号文本¶

Redshift¶

Query¶

Result¶

Snowflake¶

Query¶

Result¶

已知问题¶

不支持模板模式修饰符¶

相关的 EWIs¶