Convert julian date in Oracle SQL null

Question

I want to convert Julian dates to normal dates (from JDE database). I run this query:

select to_date(to_char((DATE+1900000)),'YYYYDDD') from table ;

And I get the following error:

ORA-01848: day of year must be between 1 and 365 (366 for leap year).

I know it's because some dates are null or have no values.

Can anyone help me out on this please?

Answer 1

I have replicated your query and I tried with different values. Only if the date value is "0", you will get this error:

ORA-01848: "day of year must be between 1 and 365 (366 for leap year)"

So I thought that maybe you were interested in avoid the "0" values, and you can avoid it with a simple where, like this one:

SELECT to_date(to_char((DATE+1900000)),'YYYYDDD') 
FROM table 
WHERE DATE != 0;