Oracle中的CASE和TO_DATE

Question

我有一个关于甲骨文案例的问题。我有一个表，假设有两列，并希望基于这两列创建第三列。

第一列有天和月的组合，存储为数字，例如2305 --> 5月23日，503 --> 3月5日等。这些值都是有效的天或空。第二列包含年份(存储为NUMBER，不为空)，例如2019、2018。现在我想从这两个列中创建一个日期列，这样如果第一列中的值为NULL，那么新列就应该为NULL，否则将第一列和第二列连接为日期。例如。

day   year       result
2305  2018   --> 23/05/2018
505   2019   --> 05/05/2019
NULL  2020   --> NULL

我试过了：

CASE WHEN day IS NOT NULL THEN TO_DATE (LPAD (TO_CHAR (day || year), 8, '0'), 'dd/mm/yyyy') ELSE NULL END AS date

但它会抛出错误"ORA-01847天必须介于1和月的最后一天之间“。

我用谷歌搜索了一下，发现Stack Overflow有时会在检查WHEN条件之前评估CASE-Statement中的"THEN“表达式。这可能是问题所在，因为当day为空时，LPAD给出的结果不能转换为日期。有没有办法绕过这一点？理想情况下，我希望有一些命令告诉Oracle首先检查day是否为空，如果不为空，则只计算结果。还是有别的方法来解决我的问题？

诚挚的问候。

Answer 1

请使用以下查询，

使用LPAD()

select  case when day is null then null else to_char(to_date(lpad(date1, 4, 0)||year1, 'ddMMyyyy'), 'dd/MM/YYYY') as result from table;

LPAD示例：

select to_char(to_date(lpad(date1, 4, 0)||year1, 'ddMMyyyy'), 'dd/MM/YYYY') from
(select '505' as date1, 2019 as year1 from dual
union all
select '2305' as date1, 2018 as year1 from dual);

使用CASE

select case when day is null then null else 
to_date((case when length(day) = 3 then '0'||ltrim(rtrim(day)) else ltrim(rtrim(day)) 
end)||ltrim(rtrim(year)), 'ddMMyyyy') end as result 
from table;

案例示例：

select to_date((case when length('505') = 3 then '0'||ltrim(rtrim('505')) else ltrim(rtrim('505')) end)||'2019', 'ddMMyyyy') as result from dual;
select to_date('2305'||'2019', 'ddMMyyyy') as result from dual;  

Answer 2

如果您想这样做，您应该首先使用TO_CHAR将day和year字段转换为文本，然后向左填充最多1个零的day，最后在这两个字段之间的连接上调用TO_DATE：

SELECT
    day,
    year,
    CASE WHEN day IS NOT NULL
         THEN TO_DATE(LPAD(TO_CHAR(day), 4, '0') || TO_CHAR(year), 'ddmmyyyy') END AS "date"
FROM yourTable;

​

​

​

Demo

​

但请注意，如果您只保留一个适当的日期列来存储日期信息，您的生活将会容易得多。