New to JDE - Need Date/Time Conversion Help

[crswain]

I'm new to JDE and have a requirement to extract some data from a general table, F5801, that contains location information.

I want to pull records based on the update date and time columns, GNUPMJ and GNUPMT, respectively.

These columns are defined as numeric(18,0) and float.

Sample data:

GNUPMJ GNUPMT
---------- ----------
104188 180000.0
104096 125403.0
104156 165157.0
104183 190355.0

Database platform is Oracle.

How do I convert these fields into a MM/DD/YYYY:HH:MM:SS to compare them in a where clause?'

I need to....

select * from F5801
where GNUPMJ || GNUPMT >= to_date('01/26/05:00:00:00', 'MM/DD/YYYY:HH:MM:SS')

(I need to know how to convert and concat these two fields to Oracle datetime format)

I have been told these are Julian dates/times but they do not conform to any format I have seen before.

Please help.

 

[crswain]

I found several posts that imply I need some complex routine to convert the date?

Since our system is on Oracle, I'm assuming I'm going to need a stored procedure / package?

Does anyone know, is there one already created as part of the JDE database implementation that I can call?

 

[Mike Dupaix]

104188 is a julian date and broken as such CYYDDD where C is the century ID, YY is the year and DDD is the day of the year (day 188 of 365). There are conversion tools and business functions that will perform the translation.

crswain <[email protected]> wrote:I'm new to JDE and have a requirement to extract some data from a general table, F5801, that contains location information.

I want to pull records based on the update date and time columns, GNUPMJ and GNUPMT, respectively.

These columns are defined as numeric(18,0) and float.

Sample data:

GNUPMJ GNUPMT

 

[cbruce]

For the numbers you showed in your post:

JDE Julain Date = (Year-1900)*1000 + DayOfYear

Where Year is the actual 4-digit year (such as 2004)
and DayOfYear is the day of the year (between 1 and 365)

You could write a PL/SQL that does the above calculation. (You probably can get away with hardcoding the 1900 offset date.)

My product, Data Access Studio does this conversion in one-step--i.e. it converts the two fields into one DateTime field. However, you have to do all your queries in Data Access Studio to use this feature (which may or may not be what you wanted.)

HTH

 

[crswain]

Thanks for your reply.

While searching the web for an answer I found these two stored procs from this link...

http://www.myriadit.co.nz/jdedates.htm

------------------------

create or replace
function jde2date( jd_date in number ) return char
is
thedate char(9) default ' ';
begin
if jd_date <> 0 then
thedate := to_char(to_date((to_char(jd_date+1900000)),'yyyyddd'));
end if;
return thedate;
end;
/

create or replace
function date2jde(theDate in date) return integer
is
jdedate integer default 0;
begin
jdedate := to_number(to_char(theDate,'yyyyddd'))-1900000;
return jdedate;
end;
/

-------------------------

I'm thinking that I should be able to do the following in-line select ....

select * from F5801
where to_date(to_char(GNUPMJ+1900000)),'yyyyddd') >= to_date('01/26/05', 'MM/DD/YYYY')

(I think Oracle will convert from YYYYDDD to MM/DD/YYYY on the fly?)

Any thoughts on if this correct?

This doesn't fully give me what I need (as I need the time component as well)... but it at least gives me a starting point.