Built-in functions

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

Oracle

Snowflake

Notes

ABS

ACOS

ADD_MONTHS

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_AGG

*to be defined

APPROX_COUNT_DISTINCT_DETAIL

*to be defined

APPROX_MEDIAN

*to be defined

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

ASCIISTR

*to be defined

ASIN

ATAN

ATAN2

AVG

BFILENAME

*to be defined

BIN_TO_NUM

*to be defined

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_BUCKET_NUMBER

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

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

BIT_OR_AGG

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

BIT_XOR_AGG

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

CARDINALITY

*to be defined

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

CHARTOROWID

*to be defined

CHECKSUM

*to be defined

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

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

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

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

COSH

COUNT

COVAR_POP

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

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

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

CURRENT_DATE

CURRENT_TIMESTAMP

*to be defined

DATAOBJ_TO_MAT_PARTITION

*to be defined

DATAOBJ_TO_PARTITION

*to be defined

DBTIMEZONE

*to be defined

DECODE

DECOMPOSE

*to be defined

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

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

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

FLOOR

FROM_TZ

*to be defined

GREATEST

GROUP_ID

*to be defined

GROUPING

GROUPING_ID

HEXTORAW

*to be defined

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

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_VALUE

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

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

LENGTH

LISTAGG

The overflow clause is removed from the function.

LNNVL

*to be defined

LOCALTIMESTAMP

LOG

LOWER

LPAD

LTRIM

MAKE_REF

*to be defined

MAX

MEDIAN

MIN

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

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

NTILE

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

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

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

PERCENTILE_CONT

PERCENTILE_DISC

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

RANK

RATIO_TO_REPORT

RAWTOHEX

*to be defined

RAWTONHEX

*to be defined

REF

*to be defined

REFTOHEX

*to be defined

REGEXP_COUNT

REGEXP_INSTR

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

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

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

REMAINDER

*to be defined

REPLACE

REVERSE

ROUND

ROUND_TIES_TO_EVEN

*to be defined

ROW_NUMBER

RPAD

ROWIDTOCHAR

*to be defined

ROWIDTONCHAR

*to be defined

RTRIM

SCN_TO_TIMESTAMP

*to be defined

SESSIONTIMEZONE

*to be defined

SET

*to be defined

SIGN

SINH

SKEWNESS_POP

*to be defined

SKEWNESS_SAMP

*to be defined

SOUNDEX

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_POP

STDDEV_SAMP

SUBSTR

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

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

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)

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

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

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_USING

TREAT

*to be defined

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

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_SAMP

VARIANCE

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

VSIZE

*to be defined

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:

Format

Conversion

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)

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

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') || TO_CHAR(DATE '1998-12-25',', MMMM DD, YYYY')

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') || '/' || LTRIM(TO_CHAR(DATE '1998-12-25', 'DD'), '0') || '/' || TO_CHAR(DATE '1998-12-25', 'YYYY')

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

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)

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

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

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

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

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

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

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

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' || TO_CHAR(SECOND(TIMESTAMP '1998-12-25 09:26:50.12')) ELSE TO_CHAR(SECOND(TIMESTAMP '1998-12-25 09:26:50.12')) END

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)

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

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)

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)

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) || ',' || SUBSTR(TO_CHAR(YEAR(DATE '1998-12-25')), 2, 3)

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

PreviousROWNUM NextSnowConvert Custom UDFs

Last updated 8 months ago