SEO

#1 (**permalink**) 02-27-2008, 08:19 AM

Hi,

Has anybody ever created a DB2 date function to convert a JDEdwards
date to a db2 date?

Example:
September 28 is 106271 in JDEdwards.

#2 (**permalink**) 02-27-2008, 08:20 AM

Why don't you figure it out yourself. If you know the JDE numeric date
for a given Gregorian date, then the conversion is quite simple.
1. Obtain the starting date for JDE's form of a Julian date
2. Write a function to do the conversion.

Or you can use the following as a starting point:

date(626311+106271)

Phil Sherman

no_spam_for_gman wrote:
> Hi,
>
> Has anybody ever created a DB2 date function to convert a JDEdwards
> date to a db2 date?
>
> Example:
> September 28 is 106271 in JDEdwards.
>

#3 (**permalink**) 02-27-2008, 08:20 AM

>
> no_spam_for_gman wrote:
> > Hi,
> >
> > Has anybody ever created a DB2 date function to convert a JDEdwards
> > date to a db2 date?
> >
> > Example:
> > September 28 is 106271 in JDEdwards.
> >

<Top post corrected>

Phil Sherman wrote:
> Why don't you figure it out yourself. If you know the JDE numeric date
> for a given Gregorian date, then the conversion is quite simple.
> 1. Obtain the starting date for JDE's form of a Julian date
> 2. Write a function to do the conversion.
>
> Or you can use the following as a starting point:
>
> date(626311+106271)
>
> Phil Sherman
>

Not quite that simple - the JDE Julian date function is not linear but
steps at year boundaries as the first 3 digits are the years past 1900,
and the last 3 are the day number within the year.

You need something like

date(693595) +
(106271 / 1000) years +
(((106271/1000.000000) - 106271 / 1000) * 1000) days

where 693595 corresponds to the1889-12-31 epoch.

#4 (**permalink**) 02-27-2008, 08:23 AM

Thanks for the info about non-linear composition of the JD Edwards
dates. I'd rather use the SQL math functions:

date(693595)+(JDE_Date/1000) years + mod(JDE_Date,1000) days

Phil Sherman

JohnO wrote:
>> no_spam_for_gman wrote:
>>> Hi,
>>>
>>> Has anybody ever created a DB2 date function to convert a JDEdwards
>>> date to a db2 date?
>>>
>>> Example:
>>> September 28 is 106271 in JDEdwards.
>>>
>
> <Top post corrected>
>
> Phil Sherman wrote:
>> Why don't you figure it out yourself. If you know the JDE numeric date
>> for a given Gregorian date, then the conversion is quite simple.
>> 1. Obtain the starting date for JDE's form of a Julian date
>> 2. Write a function to do the conversion.
>>
>> Or you can use the following as a starting point:
>>
>> date(626311+106271)
>>
>> Phil Sherman
>>
>
> Not quite that simple - the JDE Julian date function is not linear but
> steps at year boundaries as the first 3 digits are the years past 1900,
> and the last 3 are the day number within the year.
>
> You need something like
>
> date(693595) +
> (106271 / 1000) years +
> (((106271/1000.000000) - 106271 / 1000) * 1000) days
>
> where 693595 corresponds to the1889-12-31 epoch.
>

#5 (**permalink**) 02-27-2008, 08:23 AM

Phil Sherman wrote:
> Thanks for the info about non-linear composition of the JD Edwards
> dates. I'd rather use the SQL math functions:
>
> date(693595)+(JDE_Date/1000) years + mod(JDE_Date,1000) days
>
>
> Phil Sherman
>
>
>
> JohnO wrote:
> >> no_spam_for_gman wrote:
> >>> Hi,
> >>>
> >>> Has anybody ever created a DB2 date function to convert a JDEdwards
> >>> date to a db2 date?
> >>>
> >>> Example:
> >>> September 28 is 106271 in JDEdwards.
> >>>
> >
> > <Top post corrected>
> >
> > Phil Sherman wrote:
> >> Why don't you figure it out yourself. If you know the JDE numeric date
> >> for a given Gregorian date, then the conversion is quite simple.
> >> 1. Obtain the starting date for JDE's form of a Julian date
> >> 2. Write a function to do the conversion.
> >>
> >> Or you can use the following as a starting point:
> >>
> >> date(626311+106271)
> >>
> >> Phil Sherman
> >>
> >
> > Not quite that simple - the JDE Julian date function is not linear but
> > steps at year boundaries as the first 3 digits are the years past 1900,
> > and the last 3 are the day number within the year.
> >
> > You need something like
> >
> > date(693595) +
> > (106271 / 1000) years +
> > (((106271/1000.000000) - 106271 / 1000) * 1000) days
> >
> > where 693595 corresponds to the1889-12-31 epoch.
> >

That will never do... people will understand it!

#6 (**permalink**) 02-27-2008, 08:26 AM

I did managed to easily create the function to go back and forth. I
will provide it in case someone is looking for the logic.....

DATE to JDE Date (using DB2's current_date):
----------------------------------------------------------

DECIMAL((YEAR(current_date)-1900)*1000+DAYOFYEAR(current_date),6)

JDE Date to DATE:
------------------------
DATE(DIGITS(DECIMAL(JDE_DATE_FIELD + 1900000,7,0)))

#7 (**permalink**) 02-27-2008, 08:29 AM

On 10 Oct 2006 17:54:24 -0700, "no_spam_for_gman" <gaetan@gmail.com>
wrote:

>
>I did managed to easily create the function to go back and forth. I
>will provide it in case someone is looking for the logic.....
>
>DATE to JDE Date (using DB2's current_date):
>----------------------------------------------------------
>
>DECIMAL((YEAR(current_date)-1900)*1000+DAYOFYEAR(current_date),6)
>
>
>
>
>JDE Date to DATE:
>------------------------
>DATE(DIGITS(DECIMAL(JDE_DATE_FIELD + 1900000,7,0)))

Populate the F00365 with as many years as you need then join the
julian date field (ONDTEJ) and use the date field (ONDATE) which is
formatted or the individual fields MM, DD, YY, CC.

I have used several different attempts at converting the date (as seen
above by others) but they all seem to fall down somewhere, i.e.
outside the IBM window (1940 through 2039).

Doug Belcher

#8 (**permalink**) 02-27-2008, 08:30 AM

My functions works fine. I wasn't able to find that F00365 table you
talked about.

#9 (**permalink**) 02-27-2008, 08:31 AM

On 19 Oct 2006 04:48:56 -0700, "no_spam_for_gman" <gaetan@gmail.com>
wrote:

>My functions works fine. I wasn't able to find that F00365 table you
>talked about.

F00365 - Date Translation File
PK - "Julian Date" (ONDTEJ)

Programs - J000365 - Date Translation Generation
- P000365 - Date Translation Generation

Menu - G1532 Tenant Merge Letters
option - 12 Date Translation Generation

When you did your testing did you test beyond 01/01/2040 and before
01/01/1939?

Doug

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

JDEdwards date