CODEWITHSUNDEEP
snowflake-cloud-data-platform
Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Snowflake CAST and TRY_CAST of out-of-scope/incorrect dates

The CAST and TRY_CAST functions:

Converts a value of one data type into another data type. The semantics of CAST are the same as the semantics of the corresponding TO_ datatype conversion functions. If the cast is not possible, an error is raised.

A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e. converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.

For obviosuly incorrect input produces either an error or NULL(as expected):

SELECT CAST('aaaa-09-20' AS DATE) AS col;

Date 'aaaa-09-20' is not recognized

SELECT TRY_CAST('aaaa-09-20' AS DATE) AS col;
col
NULL

For incorrect format:

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-DD-MM';
SELECT CAST('2021-09-20' AS DATE) AS col;

Date '2021-09-20' is not recognized

SELECT TRY_CAST('2021-09-20' AS DATE) AS col;
col
NULL

The situation is different for DATE that is incorrect(outside of allowed range 1582-9999) for both CAST and TRY_CAST:

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-MM-DD';
SELECT TRY_CAST('22021-09-20' AS DATE) AS col, CAST('22021-09-20' AS DATE) AS col2;

This time there is no error but a normal output:

Classic UI:

enter image description here

SnowsightUI: enter image description here

In this scenario there is no error message/NULL value. The values are not usable anyway:

CREATE OR REPLACE TABLE t AS
SELECT TRY_CAST('22021-09-20' AS DATE) AS col, CAST('22021-09-20' AS DATE) AS col2;

Error:

Date '+22021-09-20' is not recognized

SELECT CURRENT_VERSION();
-- 6.26.1

Is there any specific reason for different handling of out-of-scope values?

EDIT:

Function 'aaaa-09-20' 22021-09-20 (SELECT) 22021-09-20 (CTAS)
CAST Error No error, unusable date Error
TRY_CAST NULL No error, unsuable date Error

Upvotes: 0

Views: 312

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10069

I don't see any differences between the CAST and TRY_CAST functions:

CREATE OR REPLACE TABLE t AS
SELECT TRY_CAST('22021-09-20' AS DATE) AS col;
-- fails with: Date '+22021-09-20' is not recognized

CREATE OR REPLACE TABLE t AS
SELECT CAST('22021-09-20' AS DATE) AS col;
-- fails with: Date '+22021-09-20' is not recognized

And this one returns the same values in both columns as you showed:

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-MM-DD';
SELECT TRY_CAST('22021-09-20' AS DATE) AS col, CAST('22021-09-20' AS DATE) AS col2;

+-------------+-------------+
|     COL     |    COL2     |
+-------------+-------------+
| 22021-09-20 | 22021-09-20 |
+-------------+-------------+

The difference is related to which layer you cast your dates. The SELECT queries are processed in Cloud Services Layer, but the CTAS query fails in the Query Processing Layer. So this is an inconsistent behaviour between CSL and WH.

An interesting test to see the difference:

CREATE OR REPLACE TABLE t AS
SELECT '22021-09-20' AS col;

SELECT TRY_CAST(col AS DATE) FROM t;
-- returns 22021-09-20

SELECT CAST(col AS DATE) FROM t;
-- returns 22021-09-20

The above queries are executed in Cloud Services Layer as they only access metadata (not the table itself), so they are completed without any errors.

CREATE OR REPLACE TABLE t AS
SELECT seq4() AS id, '22021-09-20' col FROM table(generator(ROWCOUNT=>100000));

SELECT TRY_CAST(col AS DATE) FROM t WHERE id = 50000;
-- fails with Date '+22021-09-20' is not recognized

This one fails, because this one is processed in Query Processing Layer

Upvotes: 1

Related Questions