Convert Julian date to JDE Julian date

Question

Does anybody know how to convert today's Julian date (2456425) to JDE Julian date (113133)?

Thanks

Why is this tagged SQL Server and PL/SQL (Oracle)? Are you looking for a solution in both TSQL and PL/SQL? And it might help to mention what a "JDE Julian date" is. — Pondlife, May 13 '13 at 15:52
@Pondlife - Google is your friend [here](http://bryantavey.com/2009/08/16/how-to-convert-enterpriseone-jde-julian-dates/) (although I agree that OP should have included some variety of reference to this bit of information). — Bob Jarvis - Слава Україні, May 13 '13 at 19:01
@BobJarvis Yes, of course, but I think the onus is on questioners to define (or at least link to) any terms that may not be familiar to a wider audience. — Pondlife, May 14 '13 at 12:34

score 1 · Answer 1 · answered May 13 '13 at 15:48

To me it looks like todays Julian date (at least in Oracle's view) should be 2456426. But, OK, if in your terms today (May 13, 2013) is Julian date 2456425 then the following code should get you what you want:

DECLARE 
  nJulian   NUMBER := 2456425;
  nCentury  NUMBER;
  nYear     NUMBER;
  nDay      NUMBER;
  nJDEdate  NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Date=' || TO_DATE(nJulian+1, 'J'));

  nCentury := TRUNC(TO_NUMBER(TO_CHAR(TO_DATE(nJulian+1, 'J'), 'YYYY')) / 100) - 19;

  DBMS_OUTPUT.PUT_LINE('nCentury=' || nCentury);

  nYear := TO_NUMBER(TO_CHAR(TO_DATE(nJulian+1, 'J'), 'YYYY')) - 
          (TRUNC(TO_NUMBER(TO_CHAR(TO_DATE(nJulian+1, 'J'), 'YYYY')) / 100) * 100);

  DBMS_OUTPUT.PUT_LINE('nYear=' || nYear);

  nDay := TO_NUMBER(TO_CHAR(TO_DATE(nJulian+1, 'J'), 'DDD'));

  DBMS_OUTPUT.PUT_LINE('nDay=' || nDay);

  nJDEdate := (nCentury * 100000) + (nYear * 1000) + nDay;

  DBMS_OUTPUT.PUT_LINE('nJDEdate=' || nJDEdate);
END;

Share and enjoy.

Convert Julian date to JDE Julian date

1 Answers1