JDEdwards date

[no_spam_for_gman]

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.

[Phil Sherman]

Re: JDEdwards date

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+106 271)

Phil Sherman


no_spam_for_gma n 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.
>

[JohnO]

Re: JDEdwards date

>
no_spam_for_gma n 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+106 271)
>
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.

[Phil Sherman]

Re: JDEdwards date

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

date(693595)+(J DE_Date/1000) years + mod(JDE_Date,10 00) days


Phil Sherman



JohnO wrote:

>no_spam_for_gm an 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+10 6271)
>>
>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.
>

[JohnO]

Re: JDEdwards date


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)+(J DE_Date/1000) years + mod(JDE_Date,10 00) days
>
>
Phil Sherman
>
>
>
JohnO wrote:

no_spam_for_gma n 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+106 271)
>
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!

[no_spam_for_gman]

Re: JDEdwards date


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(c urrent_date)-1900)*1000+DAYO FYEAR(current_d ate),6)




JDE Date to DATE:
------------------------
DATE(DIGITS(DEC IMAL(JDE_DATE_F IELD + 1900000,7,0)))

[DougDB]

Re: JDEdwards date

On 10 Oct 2006 17:54:24 -0700, "no_spam_for_gm an" <gaetan@gmail.c om>
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+DAYO FYEAR(current_d ate),6)
>
>
>
>
>JDE Date to DATE:
>------------------------
>DATE(DIGITS(DE CIMAL(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

[no_spam_for_gman]

Re: JDEdwards date

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

[DougDB]

Re: JDEdwards date

On 19 Oct 2006 04:48:56 -0700, "no_spam_for_gm an" <gaetan@gmail.c om>
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