We have an Iseries file that was created by using Client Access file transfer to transfer an Excel spreadsheet to the Iseries. The Excel file has some columns that are of format type DATE but file transfer converted them to a numeric decimal field. I'm sure whoever executed the file transfer set it up wrong. Example 3/11/2017 in Excel became 42805 in the file that was created. I would prefer not to have to download the file again and have file transfer handle the dates differently. Does anyone know of a way to convert this field back to a date in an RPG program, i.e convert 42805 back to 3/11/2017. I was hoping there was a %BIF, API, C-function or homegrown RPG routine that somebody wrote that would do this.
Announcement
Collapse
No announcement yet.
Excel date conversion in RPG
Collapse
X
-
I don't know enough about Excel dates to have a reliable answer. However, if I run this in STRSQL:
Code:select (date('1899-12-31') + 42805 days) from sysibm.sysdummy1
Code:Date expression 2017-03-12
Tom
There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.
Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?
-
Yes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.
This is the code that I use (from my HSSFR4 service program):
Code:*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * ss_xls2date(): * service program utility to convert an Excel date to * an RPG date field * * peXls = Number used as a date in Excel * * returns the RPG date *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P ss_xls2date B EXPORT D ss_xls2date PI D D peXls like(jdouble) value D wwStrDate s d inz(d'1900-01-01') D wwDate s d ** ** See ss_date2xls for comments on how the Excel date format works ** /free wwDate = wwStrDate + %days(%int(peXls) - 1); // Excel incorrectly thinks that 1900-02-29 is // a valid date. if (wwDate > d'1900-02-28'); wwDate = wwDate - %days(1); endif; return wwDate; /end-free P E
Note: jdouble is defined as "8F" (8-byte floating point)
Comment
-
Originally posted by Scott Klement View PostYes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.
Comment
-
Originally posted by Scott Klement View PostYes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.
This is the code that I use (from my HSSFR4 service program):
Code:*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * ss_xls2date(): * service program utility to convert an Excel date to * an RPG date field * * peXls = Number used as a date in Excel * * returns the RPG date *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P ss_xls2date B EXPORT D ss_xls2date PI D D peXls like(jdouble) value D wwStrDate s d inz(d'1900-01-01') D wwDate s d ** ** See ss_date2xls for comments on how the Excel date format works ** /free wwDate = wwStrDate + %days(%int(peXls) - 1); // Excel incorrectly thinks that 1900-02-29 is // a valid date. if (wwDate > d'1900-02-28'); wwDate = wwDate - %days(1); endif; return wwDate; /end-free P E
Note: jdouble is defined as "8F" (8-byte floating point)
Thanks again
Comment
-
Originally posted by arrow483 View PostFWIW: Excel is right. 1900 was NOT a leap year.
That is, Excel adds an erroneous extra day after 28-Feb-1900 that needs to subtracted back off when converted to a DB2 for i DATE. Excel treats 1900 as a leap year and adds a day that didn't exist, so the subtraction is needed for correction.Tom
There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.
Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?
Comment
Comment