CASE and TO_DATE in Oracle

Question

I have a question regarding CASE in Oracle. I have a table with, say, two columns and want to create a third column based on those two.

The first column has a combination of a day and a month, stored as NUMBER, e.g. 2305 --> 23rd of May, 503 --> 5th of March etc. Those values are all valid days or NULL. The second column has a year (stored as NUMBER, NOT NULL), e.g. 2019, 2018. I want now to make a DATE column out of the two, such that if the value in the first column is NULL, then the new column should be NULL, otherwise the concaternation of the first and second column as a DATE . E.g.

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

I tried:

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

but it throws the error "ORA-01847 day of month must be between 1 and last day of month".

I googled and found on Stack Overflow that sometimes, the "THEN" expression in a CASE-Statement is evaluated before checking for the WHEN condition. This could be the problem as LPAD gives a result not convertible into a DATE when day is NULL. Is there a way to circumvent this? Ideally, I would like some command which tells Oracle to first check if day is NULL or not and then only evaluate the result if it is not NULL. Or is there another way for my problem?

Best regards.

Answer 1

Please use below query,

Using 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;

Example for 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);

Using 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;

Example for CASE:

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

If you want to go this route, you should be first converting the day and year fields to text using TO_CHAR, then left padding day with up to one zero, and finally calling TO_DATE on the concatenation between these two fields:

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

But note that your life would be much easier if you just kept a single proper date column to store your date information.