Convert Julian Date to Gregorian Date (SQL)

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.

You can get your database administrator to convert 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

Hope this helps all

Related posts:

1. Convert UTC Time Format to Local Date and Time format
2. Calculate Begin and End Dates for Month/Year
3. Changing the Century for Calendar or Defaults for Effective dates
4. Change Database values using MS Access (JDE E1 Oracle)
5. Value of SV File_IO_Status in Table IO