Home » Applications » PeopleSoft, JD Edwards & Siebel » JDE Julian date different than Oracle's Julian date?
icon13.gif  JDE Julian date different than Oracle's Julian date? [message #208966] Tue, 12 December 2006 14:58 Go to next message
d0hboy
Messages: 5
Registered: December 2006
Junior Member
Hi -- We run a JDE application which stores it's dates in modified Julian format.

I say modified because it's not the formal definition that Oracle's date functions adhere to. The app seems to stores dates in CYYDDD, where

C = Number of Centuries since 1900
YY = Number of years since century
DDD = days since beginning of year.

I'd like to know how to convert from a modified Julian date to Oracle's Date Time to a modified Julian Date. Are there any canned procedures or functions that exist within metalink that can convert between JDE's date format and Oracle's date format? I would think that somebody's seen this before.

(reference to google group conversation)

Re: JDE Julian date different than Oracle's Julian date? [message #209311 is a reply to message #208966] Thu, 14 December 2006 04:11 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi

I had that problem ages ago...looked thrue my old code.
Havent worked with JDE for ages.....
Im not 100% sure this is what you want....but have a go

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;
END;
/
Re: JDE Julian date different than Oracle's Julian date? [message #209414 is a reply to message #209311] Thu, 14 December 2006 13:21 Go to previous messageGo to next message
d0hboy
Messages: 5
Registered: December 2006
Junior Member
Thanks for the response. I ended up doing the conversion via SQL, but I could use a pl/sql variant of it too (that was my next step).


select
19 + substr( gldgj, 1, 1 ) || -- centuries since 1900
substr(gldgj, 2,2) || '-' || -- years since beginning of century
to_char( to_date( substr( gldgj, 4, 3 ) , 'DDD') , 'MM' ) || '-' || -- month
to_char( to_date( substr( gldgj, 4, 3 ) - 1 , 'DDD') , 'DD' ) -- day
from crpdta.f0911

GL GLDOC GLKCO MY_DATE GLEXA
-- ------- ----- -------------------- ------------------------------
AA 105430 00010 2004-11-11 Post Due To Account 105430
AA 233345 00010 2004-11-10 Post Due To Account



(ignore the extra columns, I grabbed the output from a similar SQL.
icon13.gif  Re: JDE Julian date different than Oracle's Julian date? [message #219724 is a reply to message #209311] Thu, 15 February 2007 12:34 Go to previous messageGo to next message
rlively
Messages: 2
Registered: February 2007
Junior Member
Your solution doesn't seem to cover all the possibilities. For example, shouldn't '099290' be a date in 1999? Your SQL returns 2099. Instead of always adding 2000 to the year, it should take into account the 'C' bit at the beginning of the character string.

SELECT case when ('099290' IS NULL or '099290' < 1011) then '01/01/1900'
ELSE (
to_char(to_date(to_char(2000 + substr(to_char('099290'),2,2)) || '-01-01', 'yyyy-mm-dd') + (substr('099290',4,3) - 1), 'mm/dd/yyyy')
) END AS "TheDate"
FROM DUAL

Returns:

10/17/2099
Re: JDE Julian date different than Oracle's Julian date? [message #219725 is a reply to message #219724] Thu, 15 February 2007 12:48 Go to previous messageGo to next message
rlively
Messages: 2
Registered: February 2007
Junior Member
This looks ugly but I think it covers it. I didn't have access to create a function, so this is for an inline SQL statement. It also returns as a formatted character string rather than a DATE data type. Take out the wrapping to_char if you want a date.

Instead of adding 2000 to substr(2,2) to figure out the year, it adds 1900 + (100 * substr(1,1)) + substr(2,2) to get the year. So if the first bit is 0, the century will be 1900. If the first bit is 1, the century will be 2000, and so forth.

SELECT ...

case when (julian IS NULL or julian < 1011) then '01/01/1900'
ELSE (
to_char(to_date(to_char((1900 + (100 * TO_Number(substr(to_char(julian),1,1)))) + substr(to_char(julian),2,2)) || '-01-01', 'yyyy-mm-dd') + (substr(julian,4,3) - 1), 'mm/dd/yyyy')
)
END AS "TheDate"

FROM ...
Re: JDE Julian date different than Oracle's Julian date? [message #219761 is a reply to message #219725] Thu, 15 February 2007 19:38 Go to previous messageGo to next message
d0hboy
Messages: 5
Registered: December 2006
Junior Member
rlively wrote on Thu, 15 February 2007 12:48

...
Instead of adding 2000 to substr(2,2) to figure out the year, it adds 1900 + (100 * substr(1,1)) + substr(2,2) to get the year. So if the first bit is 0, the century will be 1900. If the first bit is 1, the century will be 2000, and so forth.
...


I will definitely take a closer look at your revision, as it makes sense. I had the same question, but I had assumed there was some sort of year 1950/2050 cut-off somewhere in JDE's Julian rules. Either way, thank you for bringing this up -- it's still useful to think about for me, as I am going to need this data when trying to prune out certain dates out of a table.
Re: JDE Julian date different than Oracle's Julian date? [message #227865 is a reply to message #208966] Thu, 29 March 2007 21:06 Go to previous messageGo to next message
lwkoon
Messages: 1
Registered: March 2007
Location: Malaysia
Junior Member
A VB written JDE Julian Date Converter

http://mirror.edonesolutions.com/JulianDateConverter.exe
Re: JDE Julian date different than Oracle's Julian date? [message #240219 is a reply to message #219725] Thu, 24 May 2007 07:05 Go to previous message
mieslep
Messages: 1
Registered: May 2007
Junior Member
Okay, being completely new to JDE but quite experienced with 'normal' Oracle-based applications, this date format thing threw me for a loop (and brought back nightmares of the early 1990s when I was working on AS/400s). There are a few things I'm not clear on, the documentation being pretty light-on-the-ground where this is concerned.

Can I first propose the following solution to the above problem using Oracle built-in SQL functions (and avoiding CASE for those pre-9i installations):

create table mynum (c1 number(6)); -- JDE tables define dates as NUMBER(6)
insert into mynum values (1); -- 01-JAN-1900
insert into mynum values (1365); -- 31-DEC-1901
insert into mynum values (102365); -- 31-DEC-2002
insert into mynum values (999365); -- 31-DEC-2899
commit;

create view mynum_v as
select c1
      ,to_date(to_char(1900+to_number(substr(lpad(to_char(c1),6,'0'),1,3)))
               ||substr(lpad(to_char(c1),6,'0'),4,3)
              ,'YYYYDDD') asdate
from mynum;

select c1,to_char(asdate,'YYYY-MON-DD') from mynum_v;



And then ask a few questions.

1) Is there a reason you didn't LPAD your numbers?

2) If the first digit is meant to be "centuries since 1900" and the next two are "years since the beginning of the century", why did you not simply choose to add the first three digits to 1900?

3) I didn't quite get the '<1011 = 01/01/1900' bit...is there some JDE rules that say all of 1900 and the first 10 days of 1901 are to be treated as January 1st 1900?
Previous Topic: login with hyperlink?
Next Topic: Creating NER Business function
Goto Forum:
  


Current Time: Thu Mar 28 16:14:00 CDT 2024