求助解决Databricks SQL类型不匹配错误：日期运算语法问题

错误原因 #

在Databricks的Spark SQL中，两个日期直接相减会返回Interval（时间间隔）类型，而非整数，因此无法直接与整数1进行加法运算。需要用专门的日期差函数将间隔转换为整数天数后再计算。另外原代码中CASE语句的字符串列表存在语法错误（单引号缺失、多余空格/单引号），也需要一并修正。

修正后的完整SQL代码 #

select 
  Member_ID, 
  Enroll_Date, 
  DisEnroll_Date, 
  Plan_Premium_Amount Mth_Prem, 
  Rate_Area_ID, 
  Commercial_Plan_ID, 
  Case When substr(COMMERCIAL_PLAN_ID, 1, 10) in (
    '58185SC118', '58185SC218', '58185SC338', 
    '58185SC418'
  ) Then 'IVBU' Else 'SIG' End CIG_LOB, 
  1 + datediff(End_Date, Start_Date) Days_Cov, 
  (1 + datediff(End_Date, Start_Date)) * Day_Amt Covg_Amt 
From 
  (
    select 
      RunDate, 
      Member_ID, 
      Enroll_Date, 
      DisEnroll_Date, 
      Plan_Premium_Amount, 
      Rate_Area_ID, 
      Commercial_Plan_ID, 
      Case when Enroll_Date > '20141231' Then To_Date(enroll_Date, 'YYYYMMDD') Else To_Date('20150101', 'YYYYMMDD') End Start_Date, 
      Case when DisEnroll_Date > '20150930' Then To_Date('20150930', 'YYYYMMDD') Else To_Date(Disenroll_Date, 'YYYYMMDD') End End_Date, 
      Plan_Premium_Amount / 30.4 Day_Amt 
    From 
      bx_rmr.rag_cig_enrollment 
    Where 
      RunDate > 20151101 
      and Enroll_Date < 20150930 
      and Disenroll_Date > 20141231
  )

关键修改点 #

1. 用datediff(End_Date, Start_Date)替代End_Date - Start_Date：该函数直接返回两个日期之间的整数天数差，可与整数正常运算。
2. 修正CASE语句中的字符串语法：补全缺失的单引号，移除多余的空格和单引号，确保列表格式正确。

内容的提问来源于stack exchange，提问作者D.O.