为何YYYYMMDD格式在Databricks中解析失败？求解决方案

问题根源 #

Spark 3.0及以上版本采用的Java DateTimeFormatter严格遵循ISO标准，和Oracle的日期格式规则存在差异：

• Oracle里的YYYY代表日历年份，而Spark中YYYY是基于周的年份，仅用于搭配周格式（如ww）使用，对应日历年份的格式是yyyy
• 原查询中Enrol_Date/Disenrol_date为数字类型（如20220101），直接传入to_date会被Spark当作数值处理，无法匹配日期格式规则

修改后的SQL查询 #

select Plan_ID, Rate_Area_ID, MMDays/30 as MbrMth 
from (
    select substr(COMM_PLAN_ID,4,20) as Plan_ID, 
           A.RATE_AREA_ID, 
           Sum(to_date(cast(Disenrol_date as string), 'yyyyMMdd') - to_date(cast(Enrol_Date as string), 'yyyyMMdd')) as MMDays
    from BI_RA_NM.ACA_PRIORITY_ENROLLMENT A
    where SUBSTR(COMM_PLAN_ID,1,10) IN ('1887NH087', '1886NH102',  '1885NH108')
      and to_date(cast(Enrol_Date as string), 'yyyyMMdd') >= to_date('2022-01-01')
      and to_date(cast(Enrol_Date as string), 'yyyyMMdd') <= to_date('2022-08-31')
    Group By substr(COMM_PLAN_ID,4,20), A.RATE_AREA_ID
    Order by 1,2
)

关键修改点说明 #

• 替换日期格式：将YYYYMMDD改为yyyyMMdd，匹配Spark的日历年份格式规范
• 类型转换处理：对数字类型的日期字段执行cast(xxx as string)，转为字符串后再交给to_date解析，确保格式匹配
• 统一条件判断：将日期条件转为日期类型对比，避免数字与日期直接对比的隐式转换问题，逻辑更严谨

额外优化建议 #

如果可以在数据入湖阶段将Enrol_Date/Disenrol_date提前转为日期类型存储，能避免每次查询都执行类型转换，提升查询性能。

内容的提问来源于stack exchange，提问作者Darryl Odom