Convert Utime to Date

[bozo]

There doesn't seem to be a business function or API to convert a JDEUTime (aka Peoplesoft Utime in the data dictionary!) to a JDE julian date. Has anyone out there written one by any chance?

In Xe, my table conversion used to work looking at an external Oracle table: the date columns appeared as Date columns in JDE. Now, in EnterpriseOne 8.10, they appear as Utime, and I just run into incompatible data type errors when I try to feed the data into a standard Z file jde date column.

 

[bozo]

For those who are interested, I solved my problems - see JDE's website case nos. 3897816 and 4013576.

 

[DSauve]

Keith,

Only your company can access your cases on Oracle's web site. Was a SAR associated with these cases that contains the solution? Or did your cases refer to a solution document?

 

[bozo]

Oops. Sorry, I hadn't realised that.

1. 3897816 was ‘solved by user’ – no SAR or solution document:

We are upgrading from Xe to 8.10 and a table conversion that happily saw date columns of an Oracle table as type 'Date' now see them as 'Utime' (see attached). I need to convert these to dates to feed to a standard JDE Z-file. I can find no facility to convert Utime variables to Date variables.

/continued in next reply (can I only attach 1 document toeach reply?)...

 

[bozo]

.../continued from last posting

For some reason, when I initially tried to follow the document found on your website (attached), the only Time Zone Functions listed in Expression Manager were the ones in the document. Somehow (our administrators don't (but would like to) know how) extra ones have appeared which I can use to solve the problem: before there was only utc_get_year; now there's utc_get_year, utc_get_month all the way down to utc_get_second! Now I can convert the dates.

I tested some NER code to extract each date element, however, when I created a NER business function, I found the Utime parameter, though populated when passed in, was blank inside the function. I have therefore had to write repeated code for each date. I suggest, if you can reproduce this error, that this is a bug which you could solve in a future version.

Further to this case, I've found my whole UBE aborts when executing utc_get_year Time Zone function where the Utime parameter is NULL – so I’ve made sure this is never NULL. I haven't checked the other functions.

2. ...to follow (another attachment)...

 

[bozo]

.../continued from last posting:

2. 4013576 was (no SAR):

SOLUTION ID: 200996029 E1: BSFN: Unable to Access Foreign Table Date Column using API in 8.9 and Beyond

ISSUE:
In a custom C business function, an Oracle table has been successfully opened as a foreign table using JDB_OpenForeignTable. Subsequent reads of a date column do not correctly populate the JDEDATE data structure. This worked successfully with EnterpriseOne release Xe. In EnterpriseOne 8.10 it only works in the same way when the JDEDATE data structure is replaced with JDEUTIME data structure.

SOLUTION:
In the Oracle database the JDEDATE (non-julian) and JDEUTIME items are stored in the same format which is DATE. Now, when JDB_OpenForeignTable sees this DATE column, it does not know whether it is a JDEDATE or JDEUTIME. The decision was made to go with JDEUTIME since JDEUTIME can store more information including the time. Therefore an application that uses JDB_OpenForeignTable with the DATE column would have to use the JDEUTIME as a data structure. They can map the JDEUTIME back and forth to the JDEDATE structure with the following APIs.

jdeUTime_ConvertToJDEDate() ------ to convert from UTIME to DATE

jdeUTime_ConvertFromJDEDate() -------- to convert from DATE to UTIME

JDE also provided the attached document on how to use these functions.

 

[DSauve]

Keith,

Did you ever get a formula for converting UTIME to DATE? I have a Crystal Report that uses a UTIME field, and I've put together a formula, but I have to adjust it periodically for some reason (not just because of daylight savings time). Anyway, here's the formula I'm using currently. I'd be interested in any other versions people may have.

Note: This code is from a Crystal Reports formula. The (+ 600) is my fudge factor I have to adjust from time to time.

numbervar windate:= {UTCvariable} + 600;
numbervar wyears;
numbervar wdays;
numbervar whours;
numbervar wminutes;
numbervar wremain;

wyears:= Int (windate / 525960.0);
wremain:= windate - (525960*wyears);
wyears:= wyears + 1970;

wdays:= int(wremain / 1440);
wremain:= wremain - (1440*wdays);

whours:= int(wremain / 60);

wminutes:= wremain - (60*whours);

DateTime(datevalue(DateAdd ("d",wdays-1 ,DateValue (wyears,1 ,1 ))),TimeValue (whours,wminutes ,0 ))

 

[bozo]

Sorry, no formulae. Just the API's that JDE have given. I guess any formulae are hidden therein.