CAST和TRY_CAST函数:
将一种数据类型的值转换为另一种数据。CAST的语义与相应的TO_数据类型转换函数的语义相同如果无法进行强制转换,则会引发错误
CAST的一个特殊版本,::可用于数据类型转换的子集。它执行相同的操作(即将一种数据类型的值转换为另一种数据类别(,但返回NULL值,而不是在无法执行转换时引发错误
显然,不正确的输入会产生错误或NULL(如预期(:
SELECT CAST('aaaa-09-20' AS DATE) AS col;
无法识别日期"aaaa-09-20'">
SELECT TRY_CAST('aaaa-09-20' AS DATE) AS col;
col |
---|
空 |
我看不出CAST和TRY_CAST函数之间有任何区别:
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
这个在两列中都返回相同的值,如您所示:
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 |
+-------------+-------------+
不同之处在于你把日期选在哪一层。SELECT查询在云服务层中处理,但CTAS查询在 查询处理层一个有趣的测试来看看区别: 上面的查询是在云服务层中执行的,因为它们只访问元数据(而不是表本身(,所以它们是在没有任何错误的情况下完成的。 此操作失败,因为此操作在查询处理层中处理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
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