DB: Convert JDE Date to Oracle date

Chapulin

Active Member
Hi. I'm building some queries in PL SQL to the JDE OneWorld database and I found out that Date fields are saved in the DB as decimal numbers!! How can I convert them to really Oracle Dates so I can include them in my SQL statements like every other fields? I've already tried every to_char and to_date conversion that came up to my mind!! thanks a lot!
 
They are stored as JDE Julian dates. For example, January 1, 2005 is 105001. I.e. (year - 1900)* 1000 + day number in year. To convert this to a date you could either write a PLSQL stored function, or you could use an ODBC driver like jdeDirect which transparently converts JDE data into a natural format.

Cheers,
JohnO
 
Hi,

I did some sql statement where i wanted to have date displayed in dd/mm/yyyy format. Here is what i do to convert the JDE julian date in that format :

select
to_char(to_date(substr([JDEJulinaDate],2,5),'YYDDD'),'DD/MM/YYYY')
from dual ;

And if you want to convert the today date in JDE Julian date you can try this :

select
to_number(concat('1',to_char(sysdate,'YYDDD')))
from dual ;

NB : those statement works for Oracle.
Don't know what database you use ...

Hope this help.
 
CREATE OR REPLACE FUNCTION jde_date(p_number NUMBER) RETURN DATE
AS
-- Validate and return standard dates from JDE dates.
-- JDE format is 6 digits, where first digit indicates century, (0=1900, 1=2000, 2=2100, 3=2200, etc.)
-- Second and third digits indicate year within century, and last 3 digits indicate Julian date within the year.
-- If a non-valid date or null is passed, null is returned.
-- sample syntax: select jde_date(99046) from dual; -> returns Feb 15, 1999
v_return DATE;
v_dummy_date DATE;
BEGIN
IF p_number IS NOT NULL THEN
BEGIN
v_dummy_date := to_date(p_number+1900000,'YYYYDDD');
v_return := v_dummy_date;
EXCEPTION
WHEN OTHERS THEN v_return := null;
END;
END IF;
RETURN v_return;
END jde_date;
/

see Julian Date Converter
 
Back
Top