Built-in functions

This section shows equivalents between functions in Oracle and in Snowflake.

OracleSnowflakeNotes

ABS

ABS

ACOS

ACOS

ADD_MONTHS

ADD_MONTHS

ANY_VALUE

ANY_VALUE

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

APPROX_COUNT

*to be defined

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT_AGG

*to be defined

APPROX_COUNT_DISTINCT_DETAIL

*to be defined

APPROX_MEDIAN

*to be defined

APPROX_PERCENTILE

APPROX_PERCENTILE

APPROX_PERCENTILE_AGG

*to be defined

APPROX_PERCENTILE_DETAIL

*to be defined

APPROX_RANK

*to be defined

APPROX_SUM

*to be defined

ASCII

ASCII

ASCIISTR

*to be defined

ASIN

ASIN

ATAN

ATAN

ATAN2

ATAN2

AVG

AVG

BFILENAME

*to be defined

BIN_TO_NUM

*to be defined

BITAND

BITAND

BIT_AND_AGG

BITAND_AGG

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

BITMAP_BIT_POSITION

BITMAP_BIT_POSITION

BITMAP_BUCKET_NUMBER

BITMAP_BUCKET_NUMBER

BITMAP_CONSTRUCT___AGG

BITMAP_CONSTRUCT___AGG

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

BITMAP_COUNT

BITMAP_BIT_COUNT

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

BITMAP_OR_AGG

BITMAP_OR___AGG

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

BIT_OR_AGG

BIT_OR_AGG

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

BIT_XOR_AGG

BIT_XOR_AGG

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

CARDINALITY

*to be defined

CAST

CAST

TO_DATE

TO_NUMBER

TO_TIMESTAMP

Not Supported

The function is converted to stub 'CAST_STUB' and outputs an error, when comes with one of the following not supported statement: 'DEFAULT ON CONVERSION ERROR' or 'MULTISET'. Also, it is converted to a stub and outputs an error if the data type is not supported. The function is converted to the 'TO_NUMBER' function when the expression to cast is of type number and outputs an error indicating that the explicit cast is not possible to be done. The function is converted to the 'TO_DATE' function when the expression to cast is of type date and outputs an error indicating that the explicit cast is not possible to be done. The function is converted to the 'TO_TIMESTAMP' function when the expression to cast is of type timestamp and outputs an error indicating that the explicit cast is not possible to be done.

CEIL

CEIL

CHARTOROWID

*to be defined

CHECKSUM

*to be defined

CHR

CHR

USING NCHAR_CS statement is not supported by the Snowflake function equivalent. The clause is removed.

CLUSTER_DETAILS

*to be defined

CLUSTER_DISTANCE

*to be defined

CLUSTER_ID

*to be defined

CLUSTER_PROBABILITY

*to be defined

CLUSTER_SET

*to be defined

COALESCE

COALESCE

COLLATION

COLLATION

COLLECT

*to be defined

COMPOSE

*to be defined

CON_DBID_TO_ID

*to be defined

CON_GUID_TO_ID

*to be defined

CON_NAME_TO_ID

*to be defined

CON_UID_TO_ID

*to be defined

CONCAT

CONCAT

Every expression parameter will be inside of an NVL(expr, ' ') function to avoid an error in case one of the expressions is null.

CONVERT

*to be defined

CORR

CORR

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

CORR_S

*to be defined

CORR_K

*to be defined

COS

COS

COSH

COSH

COUNT

COUNT

COVAR_POP

COVAR_POP

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

COVAR_SAMP

COVAR_SAMP

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

CUBE_TABLE

Not Supported

Converted to a stub 'CUBE_TABLE_STUB' and an error is added.

CUME_DIST

CUME_DIST

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

CURRENT_DATE

CURRENT_DATE

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP

CV

*to be defined

DATAOBJ_TO_MAT_PARTITION

*to be defined

DATAOBJ_TO_PARTITION

*to be defined

DBTIMEZONE

*to be defined

DECODE

DECODE

DECOMPOSE

*to be defined

DENSE_RANK

DENSE_RANK

There are two kinds of syntax, aggregate syntax, and analytic syntax. The aggregate syntax is not supported and an error is added. The analytic syntax is supported but the 'SIBLINGS' keyword is removed from the 'order by' clause and a warning is added.

DEPTH

*to be defined

DEREF

*to be defined

DUMP

*to be defined

EMPTY_BLOB

*to be defined

EMPTY_CLOB

*to be defined

EXISTSNODE

*to be defined

EXP

EXP

EXTRACT (datetime)

EXTRACT (datetime)

Not supported

Kept as an EXTRACT function but outputs a warning when the function has 'MINUTE' or 'TIMEZONE_MINUTE' as the first keyword parameter. Converted to a stub 'EXTRACT_STUB' and outputs an error when the first keyword parameter is 'TIMEZOME_REGION' or 'TIMEZONE_ABBR'

EXTRACT (XML)

Not Supported

Function related to XML is not supported. It is converted to a stub 'EXTRACT_STUB' and an error is added. Please check the following link about how to handle the loading for XML:

EXTRACTVALUE

Not Supported

Converted to a stub 'EXTRACTVALUE_STUB' and an error is added.

FEATURE_COMPARE

*to be defined

FEATURE_DETAILS

*to be defined

FEATURE_ID

*to be defined

FEATURE_SET

*to be defined

FEATURE_VALUE

*to be defined

FIRST

Not Supported

The statement used to indicate that only the first or last values of the aggregate function will be returned is not supported. Outputs an error.

FIRST_VALUE

FIRST_VALUE

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

FLOOR

FLOOR

FROM_TZ

*to be defined

GREATEST

GREATEST

GROUP_ID

*to be defined

GROUPING

GROUPING

GROUPING_ID

GROUPING_ID

HEXTORAW

*to be defined

INITCAP

INITCAP

INSTR

POSITION

The order of the 'string' parameter and the 'substring' parameter is inverted. Also, the 'occurrence' parameter is removed because it is not supported and a warning is added.

ITERATION_NUMBER

*to be defined

JSON_ARRAY

*to be defined

JSON_ARRAYAGG

*to be defined

JSON

*to be defined

JSON_MERGE_PATCH

*to be defined

JSON_OBJECT

*to be defined

JSON_OBJECTAGG

*to be defined

JSON_QUERY

*to be defined

JSON_SCALAR

*to be defined

JSON_SERIALIZE

*to be defined

JSON_TABLE

Not Supported

Outputs an error: JSON_TABLE IS NOT SUPPORTED.

JSON_TRANSFORM

*to be defined

JSON_VALUE

KURTOSIS_POP

*to be defined

KURTOSIS_SAMP

*to be defined

LAG

LAG

When the value expression comes with the RESPECT | IGNORE NULLS statement, the statement is moved outside the parenthesis in order to match the Snowflake grammar.

LAST

Not Supported

The statement used to indicate that only the first or last values of the aggregate function will be returned is not supported. Outputs an error.

LAST_DAY

LAST_DAY

LAST_VALUE

LAST_VALUE

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

LEAD

LEAD

When the value expression comes with the RESPECT | IGNORE NULLS statement, the statement is moved outside the parenthesis in order to match the Snowflake grammar.

LEAST

LEAST

LENGTH

LENGTH

LISTAGG

LISTAGG

The overflow clause is removed from the function.

LN

LN

LNNVL

*to be defined

LOCALTIMESTAMP

LOCALTIMESTAMP

LOG

LOG

LOWER

LOWER

LPAD

LPAD

LTRIM

LTRIM

MAKE_REF

*to be defined

MAX

MAX

MEDIAN

MEDIAN

MIN

MIN

MOD

MOD

MONTHS_BETWEEN

MONTHS_BETWEEN_UDF

Converted to a user-defined function.

NANVL

*to be defined

NCHR

*to be defined

NEW_TIME

*to be defined

NEXT_DAY

NEXT_DAY

NLS_CHARSET_DESCL_LEN

*to be defined

NLS_CHARSET_ID

*to be defined

NLS_CHARSET_NAME

*to be defined

NLS_COLLATION_ID

*to be defined

NLS_COLLATION_NAME

*to be defined

NLS_INITCAP

*to be defined

NLS_LOWER

*to be defined

NLS_UPPER

*to be defined

NLSSORT

COLLATE

Not Supported

When the function is outside of a 'where' or 'order by' clause, it is not supported and it is converted to stub 'NLSSORT_STUB' and an error is added. Otherwise, if the function is inside a 'where' or 'order by' clause, it is converted to the COLLATE function.

NTH_VALUE

NTH_VALUE

NTILE

NTILE

NULLIF

NULLIF

NUMTODSINTERVAL

Not Supported

While the function itself is not supported, some usages can be migrated manually. For example DATEADD can be used to manually migrate a sum between a Date/Timestamp and this function.

NUMTOYMINTERVAL

Not Supported

While the function itself is not supported, some usages can be migrated manually. For example DATEADD can be used to manually migrate a sum between a Date/Timestamp and this function.

NVL

NVL

NVL2

NVL2

ORA_DM_PARTITION_NAME

*to be defined

ORA_DST_AFFECTED

*to be defined

ORA_DST_CONVERTED

*to be defined

ORA_DST_ERROR

*to be defined

ORA_HASH

Not Supported

Converted to a stub 'ORA_HASH_STUB' and an error is added.

PATH

*to be defined

PERCENT_RANK

PERCENT_RANK

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

PERCENTILE_CONT

PERCENTILE_CONT

PERCENTILE_DISC

PERCENTILE_DISC

POWER

POWER

POWERMULTISET

*to be defined

POWERMULTISET_BY_CARDINALITY

*to be defined

PREDICTION

*to be defined

PREDICTION_BOUNDS

*to be defined

PREDICTION_COST

*to be defined

PREDICTION_DETAILS

*to be defined

PREDICTION_PROBABILITY

*to be defined

PREDICTION_SET

*to be defined

PRESENTNNV

*to be defined

PRESENTV

*to be defined

PREVIOUS

*to be defined

RANK

RANK

There are two kinds of syntax, aggregate syntax, and analytic syntax. The aggregate syntax is not supported and an error is added. The analytic syntax is supported but the 'SIBLINGS' keyword is removed from the 'order by' clause and a warning is added.

RATIO_TO_REPORT

RATIO_TO_REPORT

RAWTOHEX

*to be defined

RAWTONHEX

*to be defined

REF

*to be defined

REFTOHEX

*to be defined

REGEXP_COUNT

REGEXP_COUNT

REGEXP_INSTR

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_REPLACE

In the replace_string parameter (the third one) is being added an extra '' symbol to escape the other one. In the match_param parameter (last one) the equivalence works like this: 'c' -> 'c' specifies case-sensitive 'i' -> 'i' specifies case-insensitive 'n' -> 's' allows the period(.), which is the match-any-character character, to match the newline character 'm' -> 'm' treats the source string as multiple lines 'x' -> 'e' ignores whitespace characters

REGEXP_SUBSTR

REGEXP_SUBSTR

In the replace_string parameter (the second one) is being added an extra '' symbol to escape the other one. In the match_param parameter the equivalence works like this: 'c' -> 'c' specifies case-sensitive 'i' -> 'i' specifies case-insensitive 'n' -> 's' allows the period(.), which is the match-any-character character, to match the newline character 'm' -> 'm' treats the source string as multiple lines 'x' -> 'e' ignores whitespace characters

REGR

REGR

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

REMAINDER

*to be defined

REPLACE

REPLACE

REVERSE

REVERSE

ROUND

ROUND

ROUND_TIES_TO_EVEN

*to be defined

ROW_NUMBER

ROW_NUMBER

RPAD

RPAD

ROWIDTOCHAR

*to be defined

ROWIDTONCHAR

*to be defined

RTRIM

RTRIM

SCN_TO_TIMESTAMP

*to be defined

SESSIONTIMEZONE

*to be defined

SET

*to be defined

SIGN

SIGN

SINH

SINH

SKEWNESS_POP

*to be defined

SKEWNESS_SAMP

*to be defined

SOUNDEX

SOUNDEX

SQRT

SQRT

STANDARD_HASH

*to be defined

STATS_BINOMIAL_TEST

*to be defined

STATS_CROSSTAB

*to be defined

STATS_F_TEST

*to be defined

STATS_KS_TEST

*to be defined

STATS_MODE

*to be defined

STATS_MW_TEST

*to be defined

STATS_ONE_WAY_ANOVA

*to be defined

STATS_T_TEST

*to be defined

STATS_WSR_TEST

*to be defined

STDDEV

STDDEV

STDDEV_POP

STDDEV_POP

STDDEV_SAMP

STDDEV_SAMP

SUBSTR

SUBSTR

All the types of SUBSTR (SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4) are being converted to SUBSTR

SUM

SUM

SYS_CONNECT_BY_PATH

*to be defined

SYS_CONTEXT

CURRENT_USER CURRENT_SCHEMA CURRENT_DATABASE IS_ROLE_IN_SESSION CURRENT_CLIENT CURRENT_SESSION Not supported

Depending on the parameters of the function SYS_CONTEXT, it is converted to one of the specified functions. 'CURRENT_SCHEMA' converted to CURRENT_SCHEMA() 'CURRENT_USER' converted to CURRENT_USER() 'DB_NAME' converted to CURRENT_DATABASE() 'ISDBA' converted to IS_ROLE_IN_SESSION('DBA') 'SERVICE_NAME' converted to CURRENT_CLIENT() 'SESSIONID' converted to CURRENT_SESSION() 'GUEST' converted to IS_ROLE_IN_SESSION('GUEST') 'SESSION_USER' converted to CURRENT_USER() 'AUTHENTICATED_IDENTITY' converted to CURENT_USER()

When a parameter is not supported it is converted to stub 'SYS_CONTEXT_STUB'

SYS_DBURIGEN

*to be defined

SYS_EXTRACT_UTC

*to be defined

SYS_GUID

*to be defined

SYS_OP_ZONE_ID

*to be defined

SYS_TYPEID

*to be defined

SYS_XMLAGG

*to be defined

SYS_XMLGEN

*to be defined

TAN

TAN

TANH

TANH

TIMESTAMP_TO_SCN

*to be defined

TO_APPROX_COUNT_DISTINCT

*to be defined

TO_APPROX_PERCENTILE

*to be defined

TO_BINARY_DOUBLE

*to be defined

TO_BINARY_FLOAT

*to be defined

TO_BLOB (bfile)

*to be defined

TO_BLOB (raw)

*to be defined

TO_CHAR (character)

TO_CHAR

TO_CHAR (datetime)

TO_CHAR(datetime) Conditional Expression(CASE) Not Supported

Depending on the format parameter, the function is converted to conditional expression (CASE WHEN) or a user-defined function or kept as TO_CHAR(datetime). Sometimes the function will be between another function to get an equivalent result. When the function is not supported it is converted to stub 'TO_CHAR_STUB'. Go to To_Char(datetime) to get more information about this function.

TO_CHAR (number)

TO_CHAR (number)

If the numeric parameter is of type double or float the function is commented out and an error is added. When comes a format not supported, the format parameter is removed from the function and an error is added. Not supported formats: C L PR RN TM U V. If the function has the nlsparam parameter, it is removed from the function and an error is added.

TO_CLOB ( bfile | blob )

TO_VARCHAR

Outputs a warning to indicate the bfile/blob parameters are considered binary. Also outputs an error when the function has more than one parameter.

TO_CLOB (character)

TO_VARCHAR

Outputs a warning to indicate the bfile/blob parameters are considered binary. Also outputs an error when the function has more than one parameter.

TO_DATE

TO_DATE

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY J DDD MONTH RM DD-MON-RR DD-MON-RRRR SSSSS YYYY YYY Y

TO_DSINTERVAL

*to be defined

TO_LOB

*to be defined

TO_MULTI_BYTE

*to be defined

TO_NCHAR

*to be defined

TO_NCHAR (datetime)

*to be defined

TO_NCLOB

*to be defined

TO_NUMBER

TO_NUMBER

Not Supported

The 'DEFAULT integer ON CONVERSION ERROR' statement is removed and outputs an error,

Converted to a stub TO_NUMBER_STUB and an error is added when the 'format' parameter is not supported and also when the function has the 'nlsparam' parameter.

TO_SINGLE_BYTE

*to be defined

TO_TIMESTAMP

TO_DATE

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY J DDD MONTH RM DD-MON-RR DD-MON-RRRR SSSSS YYYY YYY Y

TO_TIMESTAMP_TZ

TO_DATE

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY J DDD MONTH RM DD-MON-RR DD-MON-RRRR SSSSS YYYY YYY Y

TO_UTC_TIMESTAMP_TZ

*to be defined

TO_YMINTERVAL

*to be defined

TRANSLATE

TRANSLATE

TRANSLATE_USING

TRANSLATE_USING

TREAT

*to be defined

TRIM

TRIM

LTRIM

RTRIM

Depending on the first parameter it will be converted to: LEADING keyword -> LTRIM TRAILING keyword -> RTRIM BOTH keyword -> TRIM None of these keywords -> keep as TRIM function. Also, the order of the 'trimsource' parameter and the 'trimcharacter' parameter is inverted, and the FROM keyword is removed from the function.

TRUNC (date)

TRUNC(date)

'DAY' expression is added as a second parameter of the function.

TRUNC (number)

TRUNC(number)

TZ_OFFSET

*to be defined

UID

*to be defined

UNISTR

TO_VARCHAR(expr)

In the expr parameter is being added the 'u' letter after every '\' symbol.

UPPER

UPPER

USER

*to be defined

USERNV

*to be defined

VALIDATE_CONVERSION

*to be defined

VALUE

Not Supported

Converted to a stub 'VALUE_STUB' and an error is added.

VAR_POP

VAR_POP

VAR_SAMP

VAR_SAMP

VARIANCE

VARIANCE

A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent.

VSIZE

*to be defined

WIDTH_BUCKET

WIDTH_BUCKET

XMLAGG

*to be defined

XMLCAST

*to be defined

XMLCDATA

*to be defined

XMLCOLATVAL

*to be defined

XMLCOMMENT

*to be defined

XMLCONCAT

*to be defined

XMLDIFF

*to be defined

XMLELEMENT

*to be defined

XMLEXISTS

*to be defined

XMLFOREST

*to be defined

XMLISVALID

*to be defined

XMLPARSE

*to be defined

XMLPATCH

*to be defined

XMLPI

*to be defined

XMLQUERY

Not Supported

XMLSEQUENCE

Not Supported

Converted to a stub 'XMLSEQUENCE_STUB' and an error is added.

XMLSERIALIZE

*to be defined

XMLTABLE

Not Supported

Outputs an error: XMLTABLE IS NOT SUPPORTED.

XMLTRANSFORM

*to be defined

Functions Details.

To_Char(datetime)

According to the format parameter, the function will be converted to:

FormatConversion

AD or BC

A.D. or B.C.

The function will be converted to a conditional expression (CASE) where the format is added as a result of the 'when' condition. For Example: from: To_Char(DATE '1998-12-25', 'AD') to: CASE WHEN YEAR(DATE '1998-12-25') < 0 THEN'BC'

CC or SCC

The function will be converted to a conditional expression where the original function body is added as a when condition but it will be between

a MOD function, after that the original function is added as a then result but contained by a SUBSTR function. For example:

from: To_Char(DATE '1998-12-25','CC') to: CASE WHEN MOD(YEAR(DATE '1998-12-25'), 100) = 0 THEN SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 1, 2)

D

The function will be converted to the snowflake function equivalent but the function body will be between the DAYOFWEEK datetime part.

For Example:

from: To_Char(DATE '1998-12-25','D')

to: TO_CHAR(DAYOFWEEK(DATE '1998-12-25') + 1)

DAY

The function will be converted to a user-defined function inside of an UPPER function. For Example: from: To_Char(DATE '1998-12-25','DAY')

to: UPPER(SNOWCONVERT.PUBLIC.FULL_DAY_NAME_UDF(DATE '1998-12-25'))

DDD

The function will be converted to the snowflake function equivalent but the function body will be between the DAYOFYEAR datetime part.

For Example: from: To_Char(DATE '1998-12-25','DDD')

to: TO_CHAR(DAYOFYEAR(DATE '1998-12-25'))

DD-MON-RR

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'DD-MON-YY'.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','DD-MON-RR')

to: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','DD-MON-YY')

DL

The function will be converted to a user-defined function plus the 'OR' operator plus snowflake equivalent keeping the function body but changing the format

to: ', MMM DD, YYYY

For example:

from: To_Char(DATE '1998-12-25','DL')

to: SNOWCONVERT.PUBLIC.FULL_DAY_NAME_UDF(DATE '1998-12-25')

DS

The function will be converted to a combination of the snowflake function

equivalent inside of the LTRIM function and the snowflake function equivalent.

All the parts combined with the 'OR' operator.

For Example:

from: To_Char(DATE '1998-12-25','DS')

to: LTRIM(TO_CHAR(DATE '1998-12-25', 'MM'), '0')

DY

The function will be converted to the snowflake function equivalent

inside of the UPPER function.

For example: from: To_Char(DATE '1998-12-25','DY') to: UPPER(TO_CHAR(DATE '1998-12-25', 'DY'))

I

The function will be converted to into the snowflake function equivalent

inside of the SUBSTR function.

For Example:

from: To_Char(DATE '1998-12-25','I')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 4, 1)

IW

The function will be converted to the snowflake function equivalent but the function body will be between the WEEKISO datetime part.

For Example:

from:To_Char(DATE '1998-12-25','IW')

to: TO_CHAR(WEEKISO(DATE '1998-12-25'))

IY

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'YY'.

For example:

from:To_Char(DATE '1998-12-25', 'IY')

to: TO_CHAR(DATE '1998-12-25', 'YY')

IYY

The function will be converted to the snowflake function equivalent

inside of the SUBSTR function and change the format to: 'YYYY'.

For Example:

from: To_Char(DATE '1998-12-25','IYY')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 2, 3)

IYYY

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'YYYY'.

For example:

from:To_Char(DATE '1998-12-25', 'IYYY')

to: TO_CHAR(DATE '1998-12-25', 'YYYY')

J

The function will be converted to a conditional expression with 'B.C.' as a 'then'

result and 'A.D.' as an else result.

For example:

from: To_Char(DATE '1998-12-25','J')

to: DATE_TO_JULIANDAYS_UDF(DATE '1998-12-25')

MI

The function will be converted to the snowflake equivalent. If the function

argument is SYSDATE it will be changed to CURRENT_TIMESTAMP, otherwise,

if it is of type date, the function will return null.

For Example:

from: To_Char(SYSDATE,'MI');

to: To_Char(CURRENT_TIMESTAMP,'MI')

MON

The function will be converted to the snowflake function equivalent inside of the UPPER function.

For Example:

from: To_Char(DATE '1998-12-25','MON')

to: UPPER(TO_CHAR(DATE '1998-12-25', 'MON'))

MONTH

The function will be converted to the snowflake function equivalent

inside of the UPPER function and change the format to: 'MMMM'.

For Example:

from: To_Char(DATE '1998-12-25','MONTH')

to: UPPER(TO_CHAR(DATE '1998-12-25', 'MMMM'))

Q

The function will be converted to the snowflake function equivalent inside of the QUARTER function.

For Example:

from: To_Char(DATE '1998-12-25','Q')

to: TO_CHAR(QUARTER(DATE '1998-12-25'))

RM

The function will be converted to a user-defined function.

For Example:

from: To_Char(DATE '1998-12-25','RM')

to: SNOWCONVERT.PUBLIC.ROMAN_MONTH_UDF(DATE '1998-12-25')

RR

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'YY'.

For Example:

from: To_Char(DATE '1998-12-25','RR')

to: TO_CHAR(DATE '1998-12-25', 'YY')

RR-MON-DD

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'YY-MON-DD'.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','RR-MON-DD')

to: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','YY-MON-DD')

RRRR

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'YYYY'.

For Example:

from: To_Char(DATE '1998-12-25','RRRR')

to: TO_CHAR(DATE '1998-12-25', 'YYYY')

SS

The function will be converted to a combination of a conditional expression and the snowflake function equivalent.

All the parts combined with the 'OR' operator. For Example: from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','SS')

to: CASE WHEN SECOND(TIMESTAMP '1998-12-25 09:26:50.12') = 0 THEN '00' WHEN SECOND(TIMESTAMP '1998-12-25 09:26:50.12') < 10 THEN '0'

SSSS

The function will be converted to the snowflake function equivalent but the

function body will be a concatenation of SECOND, MINUTE, and HOUR datetime parts.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','SSSS')

to: TO_CHAR(SECOND(TIMESTAMP '1998-12-25 09:26:50.12') + MINUTE(TIMESTAMP '1998-12-25 09:26:50.12') * 60 + HOUR(TIMESTAMP '1998-12-25 09:26:50.12') * 3600)

TS

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: 'HH:MI:SS PM'.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','TS')

to: TO_CHAR(TIMESTAMP '1998-12-25 09:26:50.12', 'HH:MI:SS PM')

W

The function will be converted to the TRUNC function with the DAYOFMONTH datetime part.

For Example:

from: To_Char(DATE '1998-12-25','W')

to: TRUNC(DAYOFMONTH(DATE '1998-12-25') / 7 + 1)

WW

The function will be converted to the TRUNC function with the DAYOFYEAR datetime part.

For Example:

from: To_Char(DATE '1998-12-25','WW')

to: TRUNC(DAYOFYEAR(DATE '1998-12-25') / 7 + 1)

Y

YYY

The function will be converted to the snowflake function equivalent

inside of the SUBSTR function and change the format to: 'YYYY'.

For Example:

from: To_Char(DATE '1998-12-25','Y')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 4, 1)

Y,YYY

The function will be converted to a combination of the snowflake function equivalent inside of the SUBSTR function and a comma symbol. All the parts combined with the 'OR' operator.

For Example:

from: To_Char(DATE '1998-12-25','Y,YYY')

to: SUBSTR(TO_CHAR(YEAR(DATE '1998-12-25')), 1, 1)

YEAR

SYEAR

The function will be converted to a user-defined function inside of an UPPER function.

For Example:

from: To_Char(DATE '1998-12-25','YEAR')

to: UPPER(SNOWCONVERT.PUBLIC.YEAR_NAME_UDF(DATE '1998-12-25'))

Last updated