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?

`to_char((DATE+1900000))` does not make any sense. What data type is the `DATE` column (and `date` is a horrible name for a date column) — , Jan 10 '18 at 10:03

score 0 · Answer 1 · answered Jan 10 '18 at 09:51

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;

1 Answers1