Home » Applications » PeopleSoft, JD Edwards & Siebel » JDE Julian date different than Oracle's Julian date?
 JDE Julian date different than Oracle's Julian date? [message #208966] |
Tue, 12 December 2006 14:58  |
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 #209414 is a reply to message #209311] |
Thu, 14 December 2006 13:21  |
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.
|
|
|
|
|
|
| Re: JDE Julian date different than Oracle's Julian date? [message #219725 is a reply to message #219724] |
Thu, 15 February 2007 12:48 |
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 |
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 #240219 is a reply to message #219725] |
Thu, 24 May 2007 07:05 |
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?
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 08:34:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
