AS400 Julian Date Conversion?

I need to upload an Excel file containing dates into JDE.
If the columns are formatted as dates in Excel (which is best for the user as they need to be able to manipulate them), when they are uploaded to the AS400 they appear as an integer, which I believe is the number of days since 1st January 1900 (or something like that).

Is there another way to upload dates to the AS400 so that they appear either as JDE style Julian dates or as MMDDYY (any sequence)?

Or failing that, is there a routine that converts an AS400 Julian date to a JDE Julian date?

I can write one, but I hate re-inventing the wheel...

Thanks in advance.

The rountine used all over JDE to convert various flavors of dates to Julian (or the other direction) is X0028. I use it all the time in my code. You can find it in just about any report that prints a date or screen that displays one. You just have to know the parms to pass in and out of it, which is easy to figure out once you see some code.

Thanks Sannan,

I looked at X0028 just in case it would do what I wanted, but although it will convert between the cyyddd Julian format and mmddyy etc, I can't see that it will convert from an Excel type date format, which is an offset in days since 01/01/1900.

I tried creating a date field (type L - not in JDE) in my physical file, downloaded the date to Excel ok, but am having data problems on the upload. I thought using a date field on both Excel and AS400 might work.

If I can get past the Client Access file transfer issues with the date format I might be home and dry...

Hi Tony,

What are you using to upload, the "Transfer Data to iSeries..." function in Excel, or something else?

If you are using the above function, then is it possible to have the user-presentable date display as either the extreme left or the extreme right column? That way, the date (as an integer) can be placed as a hidden column in the worksheet. When the worksheet is uploaded, the range can be set to exclude the extreme left or right column. Doing this should result in upload of the date as an integer while leaving the user-presentable date behind.

If this is either not possible or unacceptable to the users, I don't know how else to achieve this without coding of some sort (either a macro or RPG using X0028).

If anyone on the List has another solution (or a more practical one), please, let us know.

All the best,

-Keith Viverette, Sr.

You can use this in the transfer's Data Options:
date(digits(decimal(ltdtej +1900000,7,0))) as gregdate
where itdte is the jde Julian date you want converted.


From: [email protected] [mailto:[email protected]] On Behalf Of [email protected]
Sent: Thursday, November 15, 2007 10:47 AM
To: [email protected]
Subject: Re: AS400 Julian Date Conversion?

Hi Tony,

What are you using to upload, the "Transfer Data to iSeries..." function in Excel, or something else?

If you are using the above function, then is it possible to have the user-p resentable date display as either the extreme left or the extreme right col umn? That way, the date (as an integer) can be placed as a hidden column in the worksheet. When the worksheet is uploaded, the range can be set to exc lude the extreme left or right column. Doing this should result in upload o f the date as an integer while leaving the user-presentable date behind.

If this is either not possible or unacceptable to the users, I don't know h ow else to achieve this without coding of some sort (either a macro or RPG using X0028).

If anyone on the List has another solution (or a more practical one), pleas e, let us know.

All the best,

-Keith Viverette, Sr.

Re: RE: AS400 Julian Date Conversion?

I'll save this for future transfers. Thanks!

As to Tony's question, we have a procedure where we upload dates in the form mmddyy. I don't know whether all these steps are required, but it's been in use for years. First, autofit all existing data columns and insert a new column before all of them, even column A. Format the new columns to a width of 1. Save the file as Formatted Text (Space delimited) (*prn). Use the iSeries transfer.

Debbie

Re: RE: AS400 Julian Date Conversion?

Thanks.

I decided since I can't rely on the users to put a formula in a new column and I don't want to redesign the whole inout file to bite the bullet and go ahead and write an Excel to Julian (and vice versa just in case) conversion program.

I used to have to write this all the time 20 years ago on S/36 where dates were stored on the S/36 as an offest from 01/01/1900. Just had to recall the process...

Take the date value (number of days since 01/01/1900). Divide by 1461 (4 years including a leap year), that gives the number of 4 year periods. Multiply by 4 to get the year. Take the remainder, check if this is greater than 366, 731 or 1096, if so add an extra 1-3 to the year and deduct that many days. The Julian Date is then the Year you calculated + the Number of Days. Not too hard...

Almost completed...
The reverse is similar...

Of course once it's in JDE Julian format I can just use X0028 to convert to MMDDYY etc...

Re: RE: AS400 Julian Date Conversion?

If you are able to use Z file processing once the data is uploaded to JDE it doesn't really matter what format the date is in as long as it is numeric and consistent because you can define the position of dd,mm and yy in a DWV of P09110MAP. The Z file process will then convert the date to the JDE Julian format in the target file. Just to be sure we run a macro in Excel prior to the upload to format the date in to DD/MM/YY "DeliveryDate = Format(Cells(i, 1).Text, "dd/mm/YY")".