Wrong time to datetime conversion using ODBC - who am I to blame?

[Kaj Julius]

Hi,

When I do an import from a DB2 database that has a field of type Time the
resulting Datetime field doesn't just show the time like i should, but a
date as well.

A time of 12:40:01 is shown as 30-11-1899 12:40:01

If I then update the column and adds one month:

UPDATE table SET datetimeColumn = DateAdd("m", 1, datetimeColumn)

.... then the column correctly shows only the time 12:40:01


Why is this so? And is Microsoft to blame or is IBM?

Parties involved:

IBM iSeries server V5R3M0 (DB2 UDB for iSeries)
iSeries Access ODBC driver
Microsoft Access 2003 SP3

TIA

 

[Jeff Boyce]

What DB2 calls "Time" and what Access calls "Time" may only have that title
in common.

If you are trying to store a (date/)time data type value in Access, it will
use any amount to the left of a decimal place as the number of days since
.... !November 30, 1899!, and the time-since-midnight as the decimal fraction
to the right of the decimal place.

Then there's the issue of how you tell Access to format/display the value!
Two totally separate issues...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kaj Julius]

What DB2 calls "Time" and what Access calls "Time" may only have that
title in common.

If you are trying to store a (date/)time data type value in Access, it
will use any amount to the left of a decimal place as the number of days
since ... !November 30, 1899!, and the time-since-midnight as the decimal
fraction to the right of the decimal place.

If I enter a time value in a datetime field in a table, fx. 10:40 it usually
shows as 10:40:00 (default format). When I then change the format option to
Short Date it shows 30-12-1899. So I guess my Danish version of Access 2003
uses December 30, 1899 as a starting point. As I read your comment I would
have expected it to show November 30, 1899 - which would then be consistent
with the input from the DB2 time field.

Do we have a bug in the Danish version of Access 2003?

Then there's the issue of how you tell Access to format/display the value!
Two totally separate issues...

Yes, I am aware that you can suppress the date section of the datetime
value, but when you want to use the field in some calculation it becomes
troublesome.

 

[Jeff Boyce]

I could have easily missed the date that Access uses ... that was so long
ago and I'm fuzzy on some of those earlier versions (<G>!).

I suspect that, as with "Time", Access and DB2 use different definitions for
the "start here" value.

Provided you keep in mind that Access Date/Time values are BOTH date and
time, and are "point-in-time" data, and NOT "duration" data, you should be
able to make this work for your situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kaj Julius]

I could have easily missed the date that Access uses ... that was so long
ago and I'm fuzzy on some of those earlier versions (<G>!).

Has the base date changed since the 2003 version, then? Or is it still
December 30, 1899?

I suspect that, as with "Time", Access and DB2 use different definitions
for the "start here" value.

That may be so, but then that's an error, right?
I think I recall having read that IBMs ODBC driver presents it time field as
an ascii string, like {t'16.41.23'} or something like that. If I'm right
then isn't Access responsible for assigning the right starting point?

Can anyone from the IBM camp comment on the ODBC time field format (iSeries
Access ODBC driver/Client Access ODBC driver), please?

Provided you keep in mind that Access Date/Time values are BOTH date and
time, and are "point-in-time" data, and NOT "duration" data, you should be
able to make this work for your situation.

It's not that I can't make it work, it's just that I don't like to have to
"make it work". It's supposed to just work!

 

[Jeff Boyce]

Not that I'm aware of...

Jeff

Has the base date changed since the 2003 version, then? Or is it still
December 30, 1899?


That may be so, but then that's an error, right?
I think I recall having read that IBMs ODBC driver presents it time field
as an ascii string, like {t'16.41.23'} or something like that. If I'm
right then isn't Access responsible for assigning the right starting
point?

Can anyone from the IBM camp comment on the ODBC time field format
(iSeries Access ODBC driver/Client Access ODBC driver), please?


It's not that I can't make it work, it's just that I don't like to have to
"make it work". It's supposed to just work!

 

[Albert D. Kallal]

If I enter a time value in a datetime field in a table, fx. 10:40 it
usually shows as 10:40:00 (default format)

Obviously the driver you are using is not bringing in the date correctly. I
would contact the people who made driver... (the db2 people).

When I then change the format option to Short Date it shows 30-12-1899. So
I guess my Danish version of Access 2003 uses December 30, 1899 as a
starting point. As I read your comment I would have expected it to show
November 30, 1899 - which would then be consistent with the input from the
DB2 time field.

Try displaying the date field a as a double type field data field, what do
you get? In the debug window, go:

debug.Print CDBL(dlookup("MYDate","tblInvoices","id = 6"))

It sounds like your driver is taking the data in wrong (on the other hand,
perhaps the field on the system is formatted as some type of date+time on
the db2 system, but you don't know??

Simply test your values as per above by dispalying the date/time field as a
double. It should shed light on this.

Also, what did the db2 folks say on this matter?