Oracle转Hive：IS_DATE与TO_CHAR函数替代方案咨询

原Oracle逻辑：

DECODE(TRUE, IS_DATE(TO_CHAR(columnname , 'YYYYMMDD')), 'YYYYMMDD',NULL)

拆解逻辑 #

先将columnname转换为YYYYMMDD格式的字符串，再判断该字符串是否为合法日期：

• 合法则返回'YYYYMMDD'
• 不合法则返回NULL

Hive实现方案 #

1. 替代TO_CHAR：Hive中用date_format()函数将日期类型字段转为指定格式字符串，格式符需用小写yyyyMMdd（注意与Oracle格式符的大小写区别）：

   date_format(columnname, 'yyyyMMdd')
   

   若columnname是字符串类型，需先转成日期再格式化：

   date_format(to_date(columnname), 'yyyyMMdd')
   

2. 替代IS_DATE：Hive没有直接的IS_DATE函数，可通过try_cast()尝试将字符串按指定格式转为日期，转换成功则为合法日期，否则返回NULL：

   try_cast(date_format(columnname, 'yyyyMMdd') as date format 'yyyyMMdd') is not null
   

3. 组合完整逻辑：用IF()替代DECODE，最终SQL如下：

   IF(try_cast(date_format(columnname, 'yyyyMMdd') as date format 'yyyyMMdd') is not null, 'YYYYMMDD', NULL)
   

特殊情况处理 #

如果columnname本身是字符串类型（非日期类型），需先验证原字符串是否可转为日期，再执行后续判断：

IF(try_cast(columnname as date) is not null, 'YYYYMMDD', NULL)

内容的提问来源于stack exchange，提问作者lei kim