阿里云ODPS使用to_char()报错

执行SQL语句：select to_char(from_unixtime(cast(1622628854384/1000 as BIGINT)),'yyyymmdd');报错信息如下：FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function to_char cannot match any overloaded functi

lhxsir

2566人浏览 · 2021-07-29 15:36:17

lhxsir · 2021-07-29 15:36:17 发布

执行SQL语句：

select to_char(from_unixtime(cast(1622628854384/1000 as BIGINT)),'yyyymmdd');

报错信息如下：

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function to_char cannot match any overloaded functions with (STRING, STRING), candidates are STRING TO_CHAR(BIGINT arg0); STRING TO_CHAR(BOOLEAN arg0); STRING TO_CHAR(DATE arg0, STRING arg1); STRING TO_CHAR(DATETIME arg0, STRING arg1); STRING TO_CHAR(DECIMAL(?,?) arg0); STRING TO_CHAR(DOUBLE arg0); STRING TO_CHAR(TIMESTAMP arg0, STRING arg1); STRING TO_CHAR(VOID arg0)

解决方法：

#方法一 添加配置参数
SET odps.sql.hive.compatible =false;
select to_char(from_unixtime(cast(1622628854384/1000 as BIGINT)),'yyyymmdd');
#方法二 强制转换成datetime
select to_char(datetime(from_unixtime(cast(1622628854384/1000 as BIGINT))),'yyyymmdd');