Convert Julian Date to Gregorian Date (SQL)

Posted on by

One of the very usual things Developers and Functional people do for analysis and data confirmations is using SQL.
Database Queries have been a very integral part of every technical consultant to check data, and do simple queries like getting count of rows, trying to join tables, check the Execution plan of a JDE Report i.e. SQL generated by the Report so as to optimize it etc.
A problem with SQL over JDE Data is the way Dates are stored in JD Edwards. JDE stores dates in Julian Format. The following SQL snippet can convert the Julian date into Gregorian Date format or Normal date.

19 + SUBSTR( DATEVAL , 1, 1 )
|| SUBSTR(DATEVAL, 2,2)
|| '/'
|| TO_CHAR( to_date( SUBSTR( DATEVAL, 4, 3 ) , 'DDD') , 'MM' )
|| '/'
|| TO_CHAR( to_date( SUBSTR(DATEVAL, 4, 3 ) , 'DDD') , 'DD' )

where DATEVAL is the date you would like to use. The output is in YYYY-MM-DD format.
Another simpler way to do the conversion in Oracle SQL is

TO_DATE(TO_CHAR(DATEVAL+1900000),'YYYYDDD')

You can get your database administrator to convert any of the above SQL snippet into a Function say FNJULTODATE(dateparam), where dateparam would be the date you want to convert, and viola… you have your SQL displaying Gregorian dates
A different solution exists for Excel spread sheets. You can use the following formula to convert Julian dates to Gregorian date.

=DATE(1900+INT($CELLVALUE$/1000),1,MOD($CELLVALUE$,1000))

where $CELLVALUE$ is your cell/column you need to convert
An additional tip from Tonio – 
A reverse conversion to convert oracle system date to Julian (basically just reverse of the logic to convert to Julian) – TO_NUMBER(TO_CHAR(SYSDATE, ‘YYYYDDD’))-1900000
Hope this helps all.
Added a Snippet below to Show the JDE Julian Date.

11 thoughts on “Convert Julian Date to Gregorian Date (SQL)”

  1. Soma Gupta Reply

    Hi,
    I just stumbled on to this site while browsing through the net and it was such a refreshingly new experience. It was all the more better experience as not only the site shares some valuable tips(without a price tag)it is from one from my company.
    Its is really a credible effort and also something others should emulate.
    Thanks
    Soma

  2. Scot Loucks Reply

    Hi Deepesh;
    I have been given a load of comma delimited files from a client (too large to load into excel).
    They have excel Julian dates in all the date fields (Days since Jan. 1, 1900).
    I need a to do the math on these to at least get them to a Julian format I can can convert with current routines (on an I/Series using ILE RPG). format of YY-ddd would be great.
    I’ve been playing with it…. but hoping for a quick equation.
    Cheers
    Scot Loucks

    • Deepesh Divakaran Post authorReply

      Hi Scot
      It will be a two step process to get into YYDDD.
      Add 1/1/1900 to the Julian date.(which I assume is 40350 for 6/22/2010)
      Once you get the current Date use functions to extract the year (10)
      Get the days Difference between the start of that year (1/1/2010) and the date (6/22/2010)
      I guess this is what you asked for. Hope it helps.

  3. king Reply

    Hi,
    Great work here,
    I need your help I’m developing an application using C that convert a georgian date to julian date then have as result a julian date I need to convert these new date back to georgian date but I cant find equations to get it in yyyy mm dd hh mm ss: I can get it with up to hours minutes and seconds precision wich is crutial for my app.
    Thanks for your help and sorry for my bad english,
    Greetings.

  4. Ola Reply

    Hi,
    Could you please tell me where in access I would need to put this below code, to convert the date from julina to gregorian?
    19 + SUBSTR( DATEVAL , 1, 1 )
    || SUBSTR(DATEVAL, 2,2)
    || ‘/’
    || TO_CHAR( to_date( SUBSTR( DATEVAL, 4, 3 ) , ‘DDD’) , ‘MM’ )
    || ‘/’
    || TO_CHAR( to_date( SUBSTR(DATEVAL, 4, 3 ) , ‘DDD’) , ‘DD’ )
    Thanks in advance

  5. Tonio Thomas Reply

    Deepesh ,
    Was nice to get your website as the first one on my google search. Keep it up.. good work
    You can edit the content to also show a reverse conversion to convert oracle system date to Julian (basically just reverse of the logic to convert to Julian)
    TO_NUMBER(TO_CHAR(SYSDATE, ‘YYYYDDD’))-1900000

  6. Deepak Shanker Reply

    In case you have a 0 in the Julian date field(wrong sqls or anything) its good to have the following sql
    decode(DATEVAL,0,’0′,TO_DATE(1900000 + DATEVAL, ‘YYYYDDD’))

  7. Dale Porter Reply

    This was a great help. I always have to search internet to figure out how to convert these. Now, I’ve created functions and will no longer have to do that.

  8. Deepesh MD Reply

    SQL Server Function to convert System date to Julian
    convert(numeric,(datepart(year,SYSDATETIME()) * 1000 + datepart(dy, SYSDATETIME()))-1900000)

Leave a Reply