Oliver
Well Known Member
Hi All,
Since Larry Jones posted his JDE2DATE function (here) and have search the opposite function here in the forum and found nothing...
I created this oracle function DATE2JDE to convert a "normal date" (yyyymmdd) to JDE Julian date since I was tired of using a date conversion app or a long sql (example) when using an adhoc query in the database.
Hope you liked it.
******************** START CODE ********************
/*
AUTHOR: OLIVER GICA, PHILIPPINES
USAGE: DATE2JDE(yyyymmdd), ex. DATE2JDE(20110203)
*/
CREATE OR REPLACE FUNCTION DATE2JDE (yyyymmdd IN NUMBER)
RETURN NUMBER
IS
v_jdedate NUMBER(6,0) := 0;
BEGIN
IF yyyymmdd BETWEEN 19000000 AND 28000000 THEN
v_jdedate := TO_NUMBER(TO_CHAR(TO_DATE(yyyymmdd,'YYYYMMDD'),'YYYYDDD'))-1900000;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Invalid date in DATE2JDE - ' || yyyymmdd ||'.');
END IF;
RETURN(v_jdedate);
END;
******************** END CODE ********************
Thanks to Larry and Peter...revised to use Peter's much shorter and simpler code.
Since Larry Jones posted his JDE2DATE function (here) and have search the opposite function here in the forum and found nothing...
I created this oracle function DATE2JDE to convert a "normal date" (yyyymmdd) to JDE Julian date since I was tired of using a date conversion app or a long sql (example) when using an adhoc query in the database.
Hope you liked it.
******************** START CODE ********************
/*
AUTHOR: OLIVER GICA, PHILIPPINES
USAGE: DATE2JDE(yyyymmdd), ex. DATE2JDE(20110203)
*/
CREATE OR REPLACE FUNCTION DATE2JDE (yyyymmdd IN NUMBER)
RETURN NUMBER
IS
v_jdedate NUMBER(6,0) := 0;
BEGIN
IF yyyymmdd BETWEEN 19000000 AND 28000000 THEN
v_jdedate := TO_NUMBER(TO_CHAR(TO_DATE(yyyymmdd,'YYYYMMDD'),'YYYYDDD'))-1900000;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Invalid date in DATE2JDE - ' || yyyymmdd ||'.');
END IF;
RETURN(v_jdedate);
END;
******************** END CODE ********************
Thanks to Larry and Peter...revised to use Peter's much shorter and simpler code.