CODEWITHSUNDEEP
oracle-databasejulian-date
JohnD
JohnD

Reputation: 353

How to convert these julian dates to oracle date format?

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

Upvotes: 4

Views: 42413

Answers (4)

Jahanzeb Kibria
Jahanzeb Kibria

Reputation: 1

Simple

select to_date('0101'|| substr(TRN_REF_NO, 8,2), 'DDMMYY') + to_number(substr(TRN_REF_NO, 10,3)) - 1 Trn_dt

Upvotes: -1

Swastik Raj Ghosh
Swastik Raj Ghosh

Reputation: 668

The simplest way that I found to convert from Julian is: -

SELECT TO_CHAR(TO_DATE(your_julian_date,'JSP'),'dd-Mon-yyyy AD') AS DAY FROM dual;

Also, doesn't matter if the date field is number or a String. If a string, simply put the Julian date inside single quotes.

Also, the Oracle definition of Julian is 'number of days since January 1, 4712 BC'. (Many have erroneously commented as 4713 BC). Ref: https://asktom.oracle.com/pls/apex/asktom.search?tag=julian-date

Upvotes: 4

Diego
Diego

Reputation: 21

If your date field is varchar:

select TO_DATE(TO_CHAR(to_number('115032')+1900000),'YYYYDDD') jdedate from dual;

If your date fiel is a number:

select TO_DATE(TO_CHAR(115032+1900000),'YYYYDDD') jdedate from dual;

Both Return:

JDEDATE
-----------
01/02/2015

Upvotes: 1

Bob
Bob

Reputation: 1065

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

Upvotes: 6

Related Questions