Built-in functions

Aggregate

TransactSQL

Snowflake

Notes

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT

AVG​

AVG

CHECKSUM_AGG

*to be defined

COUNT

COUNT

COUNT_BIG

*to be defined

GROUPING

GROUPING

GROUPING_ID

GROUPING_ID

MAX

MAX

MIN

MIN

STDEV

STDDEV, STDEV_SAMP

STDEVP

STDDEV_POP

SUM

SUM

VAR

VAR_SAMP

VARP

VAR_POP​

Analytic

TransactSQLSnowflakeNotes

CUME_DIST

CUME_DIST

FIRST_VALUE

FIRST_VALUE

LAG

LAG

LAST_VALUE

LAST_VALUE

LEAD

LEAD

PERCENTILE_CONT

PERCENTILE_CONT

PERCENTILE_DISC

PERCENTILE_DISC

PERCENT_RANK

PERCENT_RANK

Collation

TransactSQLSnowflakeNotes

COLLATIONPROPERTY

*to be defined

TERTIARY_WEIGHTS

*to be defined

Configuration

TransactSQLSnowflakeNotes

​@@DBTS

*to be defined

@@LANGID

*to be defined

@@LANGUAGE

*to be defined

@@LOCK_TIMEOUT

*to be defined

@@MAX_CONNECTIONS

*to be defined

@@MAX_PRECISION

*to be defined

@@NESTLEVEL

*to be defined

@@OPTIONS

*to be defined

@@REMSERVER

*to be defined

@@SERVERNAME

CONCAT('app.snowflake.com', CURRENT_ACCOUNT( ))

@@SERVICENAME

*to be defined

@@SPID

*to be defined

@@TEXTSIZE

*to be defined

@@VERSION

*to be defined

Can be mimicked by using CURRENT_VERSION

Conversion

TransactSQLSnowflakeNotes

CAST

CAST

Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL

CONVERT

Check CONVERT

Same behavior as CAST

PARSE

*to be defined

TRY_CAST

TRY_CAST

Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL

*to be defined

Same behavior as TRY_CAST

TRY_PARSE

TRY_CAST

Behavior may be different when parsing an integer as date or timestamp.

Cryptographic

TransactSQLSnowflakeNotes

ASYMKEY_ID

*to be defined

ASYMKEYPROPERTY

*to be defined

CERTENCODED

*to be defined

CERTPRIVATEKEY

*to be defined

DECRYPTBYASYMKEY

*to be defined

DECRYPTBYCERT

*to be defined

DECRYPTBYKEY

*to be defined

DECRYPTBYKEYAUTOASYMKEY

*to be defined

DECRYPTBYKEYAUTOCERT

*to be defined

DECRYPTBYPASSPHRASE

_*to be defined_​

Can be mimicked by using DENCRYPT_RAW

ENCRYPTBYASYMKEY

*to be defined

ENCRYPTBYCERT

*to be defined

ENCRYPTBYKEY

*to be defined

ENCRYPTBYPASSPHRASE

*to be defined

Can be mimicked by using ENCRYPT_RAW

HASHBYTES

MD5, SHA1, SHA2

Currently only supported separated hash. Use proper one according to the required algorithm

MD5, is a 32-character hex-encoded

SHA1, has a 40-character hex-encoded string containing the 160-bit

SHA2, a hex-encoded string containing the N-bit SHA-2 message digest. Sizes are:

224 = SHA-224

256 = SHA-256 (Default)

384 = SHA-384

512 = SHA-512

IS_OBJECTSIGNED

*to be defined

KEY_GUID

*to be defined

KEY_ID

*to be defined

KEY_NAME

*to be defined

SIGNBYASYMKEY

*to be defined

SIGNBYCERT

*to be defined

SYMKEYPROPERTY

*to be defined

VERIGYSIGNEDBYCERT

*to be defined

Cursor

TransactSQLSnowflakeNotes

@@CURSOR_ROWS

*to be defined

@@FETCH_STATUS

*to be defined

CURSOR_STATUS

*to be defined

Data type

TransactSQLSnowflakeNotes

DATALENGTH

OCTET_LENGTH

​Snowflake doesn’t use fractional bytes so length is always calculated as 8 * OCTET_LENGTH

IDENT_SEED

*to be defined

IDENT_CURRENT

*to be defined

IDENTITY

*to be defined

IDENT_INCR

*to be defined

SQL_VARIANT_PROPERTY

*to be defined

Date & Time

TransactSQLSnowflakeNotes

@@DATEFIRST

*to be defined

@@LANGUAGE

*to be defined

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP

CURRENT_TIMEZONE

*to be defined

DATEADD

DATEADD

DATEDIFF

DATEDIFF

DATEDIFF_BIG

*to be defined

DATEFROMPARTS

DATE_FROM_PARTS

DATENAME

*to be defined

This function receives two arguments: a datepart and date. It returns a string. Here are the supported dateparts from TSQL to Snowflake

year, yyyy, yy -> DATE_PART(YEAR, "$date") quarter, qq, q -> DATE_PART(QUARTER, "$date") month, mm, m -> MONTHNAME( "$date"), thou only providing a three-letter english month name dayofyear, dy, y -> DATE_PART(DAYOFYEAR, "$date") day, dd, d -> DATE_PART(DAY, "$date") week, wk, ww -> DATE_PART(WEEK, "$date")

weekday, dw -> DAYNAME("$date"), thou only providing an three-letter english day name hour, hh -> DATE_PART(HOUR, "$date") minute, n -> DATE_PART(MINUTE, "$date") second, ss, s -> DATE_PART(SECOND, "$date") millisecond, ms -> DATE_PART(MS, "$date") microsecond, mcs -> DATE_PART(US, "$date") nanosecond, ns -> DATE_PART(NS, "$date") TZoffset, tz -> needs a special implementation to get the time offset

DATEPART

DATE_PART

DATETIME2FROMPARTS

*to be defined

DATETIMEFROMPARTS

*to be defined

​Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS

DATETIMEOFFSETFROMPARTS

*to be defined

DAY

DAY

EOMONTH

*to be defined

Can be mimicked by using LAST_DAY

FORMAT

*to be defined

Maps to TO_CHAR

GETDATE

GETDATE

GETUTCDATE

*to be defined

Can be mimicked by using CONVERT_TIMEZONE

ISDATE

*to be defined

Can be mimicked by using TRY_TO_DATE

Returns NULL if the value isn't a date, otherwise returns the date value as its. When using operators such as <, >, =, <> then must be follow by a NULL

MONTH

MONTH

SMALLDATETIMEFROMPARTS

*to be defined

​​Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS

SWITCHOFFSET

*to be defined

​Can be mimicked by using CONVERT_TIMEZONE

SYSDATETIME

LOCALTIME

SYSDATETIMEOFFSET

*to be defined

​Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME

SYSUTCDATETIME

*to be defined

​​Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME

TIMEFROMPARTS

TIME_FROM_PARTS

TODATETIMEOFFSET

*to be defined

​Can be mimicked by using CONVERT_TIMEZONE

YEAR