Gregorian (Normal) to Julian Conversion - Oracle Function

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.

Great Job kentoy!

Here's a slightly more condensed version:
--------------------------------------------------------------
CREATE OR REPLACE FUNCTION "DATE2JDE" (oradate IN DATE)
RETURN NUMBER
IS
v_jdedate NUMBER :=0;
BEGIN
IF oradate IS NULL THEN
v_jdedate := 0;
ELSE
v_jdedate := (TO_NUMBER(TO_CHAR(oradate,'YYYY')) - 1900) * 1000 + TO_NUMBER(TO_CHAR(oradate,'DDD'));
END IF;
RETURN (v_jdedate);
END;
--------------------------------------------------------------

Kentoy,

Good Work!

Here is something to make it a little simple and is what I use in my PL/SQL code:

v_jdedate := TO_NUMBER(TO_CHAR(TO_DATE(yyyymmdd,'YYYYMMDD'),'YYYYDDD'))-1900000

Thanks Larry and Peter...

Anyways, here's an updated function (shorter) courtesy of PeterBruce.

<font class="small">Code:</font><hr /><pre>
/*
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;
/
</pre><hr />

Oliver,

Here is the code I use for the reverse - JDE Julian to Gregorian (Normal) - JDEDate is the usual 6 digit number:

v_oracle_date := to_date(to_char(v_JDEDate+1900000),'YYYYDDD')
v_char_year := to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'YYYY')
v_char_month := to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'MM')
v_char_day := to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'DD')
v_num_year := to_number(to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'YYYY')
v_num_month := to_number(to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'MM')
v_num_day := to_number(to_char(to_date(to_char(v_JDEDate+1900000),'YYYYDDD'),'DD')