Coverting JDE Julian Date to Calendar Date within a select statement

Question

I have tried so many of the answers from other similar questions and I'm still getting nowhere.

I have a select statement where I am trying to convert a JDE Julian date (6 digit) to a normal calendar date.

The only one I have gotten to return anything is below but the date returned is wrong. It returned a date in 2024 when it should have been 2023-06-12 for the julian date 123163.

to_date(to_char(1900 + floor(IL.ILTRDJ / 1000)),'YYYY') + mod(IL.ILTRDJ,1000) - 1,

I am looking to have something like this:

Select firstname, lastname, Converted julian date as date position, from ....

Any help would be appreciated.

Answer 1

Here's a method which concatenates the Year with the Remainder Days in YYYYDDD format.

select 
  to_date(concat(1900 + floor(123163/1000), mod(123163,1000)),'YYYYDDD') as date_col
from dual;

DATE_COL
12-JUN-2023

fiddle