Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and the decimal portion being the time.

Are they all the same format?

closed as unclear what you're asking by mustaccio, kevinsky, MDCCL, hot2use, Marcello Miorelli May 2 at 11:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why do you care? What problem are you trying to solve with the knowledge of the internal storage format? – a_horse_with_no_name Apr 30 at 21:19
  • @a_horse_with_no_name I have a database I'm working on with that internal storage format...it's not stored as a date though, it's stored as two fields with 6 digits each one marked time and one marked date. But it's not the native DB2 date / time stamp... – leeand00 Apr 30 at 21:28
  • I figure at some point maybe people pulled stuff out of it and converted it in excel or lotus 1-2-3 and that's why it's stored like that. – leeand00 Apr 30 at 21:29
  • If you pull the data out of DB2 you will be using SQL, so you don't need to know how DB2 stores it. And I very much doubt you would write Excel or Lotus files manually byte by byte but using a library which handles that. – a_horse_with_no_name May 1 at 4:42
  • Well it wasn’t my idea, I didn’t build the database, I’m just querying it; and I have no intention of altering it or the processes that record it’s records. – leeand00 May 1 at 12:02


Db2 for i (aka AS/400) dates are stored internally as a Scaliger number (aka Julian Day number)

The number of days since noon on Monday, January 1, 4713 BC.

select hex(current_date)
from sysibm.sysdummy1

returns 002583EC as of about 3pm MDT April 30,2019

The decimal value is 2,458,604

Which agrees which the current value on the web page linked to above screen shot from Julian day page

  • Okay so AS/400 dates are not stored like that. But Lotus and Excel are? – leeand00 Apr 30 at 21:12
  • 1
    @leeand00. Yes, Excel/L123 store dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day (ddddd.tttttt) so same type of number but using a different base date. – topshot May 3 at 19:51
  • @topshot I believe there's something about it here: superuser.com/questions/736182/… – leeand00 May 3 at 19:56
  • @leeand00 Yep, the cpearson page from that answer is where I found the info in fact. ;) – topshot May 3 at 20:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.