Built-in functions
This section shows equivalents between functions in Oracle and in Snowflake.
Oracle
Snowflake
Notes
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
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:
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)
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') ||
TO_CHAR(DATE '1998-12-25',', MMMM DD, YYYY')
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') || '/' ||
LTRIM(TO_CHAR(DATE '1998-12-25', 'DD'), '0') || '/' ||
TO_CHAR(DATE '1998-12-25', 'YYYY')
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' || 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)
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) || ',' ||
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'))
Last updated