JD Edwards and BI Publisher Dates

BI Publisher EE and JD Edwards Julian Date Format
By Shahed Munir

One of my clients has JD Edwards and raised a query with me the other day regarding Julian Dates and how to utalise them in BI Publisher EE.

I wrote a function to convert the date from Julian to Gregorian Date format and then Create a BI Publisher parameter as normal....

Julian = 110001
Gregorian = 01-01-2010

-- Compile Function in Database to Convert Julian Date to Gregorian Date
create or replace
FUNCTION UTIL_Julian_To_Date(julian in int)
return date is
BEGIN
DECLARE
year int;
days int;
BEGIN
if (julian < 1001) then
return to_date('1900-01-01','yyyy-mm-dd');
end if;
year := 2000 + substr(to_char(julian),2,2);
days := substr(julian,4,3) - 1;
return to_date(to_char(year) || '-01-01', 'yyyy-mm-dd') + days;
END;

-- SQL For BI Publisher
select to_char(UTIL_Julian_To_Date(ship_date),'DD-MM-YYYY'), product
from my_table
where to_date(to_char(UTIL_Julian_To_Date(ship_date),'DD-MM-YYYY'),'DD-MM-YYYY') = :TRIGGER_DATE

-- BI Publisher EE Parameter Setup
Create a Date Parameter called Trigger Date or what ever you have called your :parameter
The Date Format Sting needs to be filled in : dd-MM-yyyy (The MM has to be Upper Case)

That worked a treat...