How to convert these julian dates to oracle date format?

julian-date, oracle

Solution

-- Using JD Edwards Date Conversions if DATE is a number

select to_date(to_char(1900 + floor(DATE / 1000)),'YYYY') + mod(DATE,1000) - 1 from dual;

-- Using JD Edwards Date Conversions if DATE is a string

select to_date(to_char(1900 + floor(to_number(DATE) / 1000)),'YYYY') + mod(to_number(DATE),1000) - 1 from dual;

Problem

Below is a sample set of data that is supposed to be dates. I am not sure what kind of format it should be but I was told they are julian dates. ``` DATE 92017 92320 99002 99003 112010 112011 112012 112013 ``` Can anyone convert them into oracle dates? I tried ``` select to_date(DATE,'J') from dual ``` but some results were in the 1950s and 1940s which doesn't seen right for the data we are dealing with. Am i doing it right here? I also tried this formula from this link: http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd ``` SELECT TO_DATE(1900+(DATE/1000),1,1)+ TO_NUMBER(SUBSTR(TO_CHAR(DATE),4))-1 FROM DUAL; ``` Thanks

Original source