Oracle Julian Date Arithmetic, JDE style

Ok, so you want to take 18 months from a Julian date, easy, just take away (365+(364/2) – yeah?  NO!  That’ll be treated as 11300-547 = 10753 – not a valid Julian date.  You need to get smarter than that…  So, this is probably the long way around, but it’s how I did it.

CREATE OR REPLACE function TESTDTA.jde_date_to_date
(     jde_date    number
)
return date
is
begin
/* Function to convert dates in JDE to oracle dates
   JDE stores it dates in a 6 digit number field
   1st 3 characters is the number of years since 1900
   next 3 characters is the day # in the year */
if nvl(jde_date,0) = 0
then
  return null;
else
  return to_date(to_char(to_number(substr(to_char(jde_date,'000000'),2,3))+1900)||substr(to_char(jde_date,'000000'),5,3),'YYYYDDD');
end if;
end jde_date_to_date;

CREATE OR REPLACE function TESTDTA.jde_date_to_char
(           jde_date            number,
            format_str          varchar2 := 'DD/MM/YYYY'
)
return varchar2
is
begin
/* Function to convert dates in JDE to format specified */
    return  to_char( jde_date_to_date(jde_date), format_str    );
end jde_date_to_char;

So, you have a couple of ripper functions, one to convert JDE date to oracle date and one to convert that result to a char!  So we put all of that together, (oh and a pinch of fairy dust – which is how to convert a oracle date to Julian date (TO_NUMBER(TO_CHAR(testdta.jde_date_to_date(ccdfyj),'yyyyddd')) – 1900000) – to create some date logic SQL that is below:

First two columns are the original Julian date and then using one of our functions, the oracle date representation of  his.  The second column uses date functions (which you care get here) to take away 18 months (yay – I don’t have to do the leap year calcs!!!!), then I convert that to a JDE Julian date!/

select ccdfyj as ORIGINAL_JULIAN,
testdta.jde_date_to_date(ccdfyj) AS ORACLE_DATE,
TO_NUMBER(TO_CHAR(testdta.jde_date_to_date(ccdfyj),'yyyyddd')) - 1900000 AS DATE_BACK_TO_JULIAN,
add_months(testdta.jde_date_to_date(ccdfyj), 18) AS Eighteen_months_before,
TO_NUMBER(TO_CHAR(add_months(testdta.jde_date_to_date(ccdfyj), 18),'yyyyddd')) - 1900000 AS DATE_18_BACK_TO_JULIAN
from testdta.F0010
/

ORIGINAL_JULIAN ORACLE_DA DATE_BACK_TO_JULIAN EIGHTEEN_ DATE_18_BACK_TO_JULIAN
--------------- --------- ------------------- --------- ----------------------
         117001 01/JAN/17              117001 01/JUL/18                 118182
         117001 01/JAN/17              117001 01/JUL/18                 118182
         117001 01/JAN/17              117001 01/JUL/18                 118182
         117001 01/JAN/17              117001 01/JUL/18                 118182
         117001 01/JAN/17              117001 01/JUL/18                 118182
         117152 01/JUN/17              117152 01/DEC/18                 118335

Nice!