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

This is a discussion on Wrong time to datetime conversion using ODBC - who am I to blame? - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 6 of 6

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

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

    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



  2. Re: Wrong time to datetime conversion using ODBC - who am I to blame?

    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" wrote in message
    news:O9pZPRX%23IHA.3392@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    >




  3. Re: Wrong time to datetime conversion using ODBC - who am I to blame?


    "Jeff Boyce" skrev i en meddelelse
    news:egNH7MY%23IHA.5012@TK2MSFTNGP03.phx.gbl...
    > 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.

    > More info, please...
    >
    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP




  4. Re: Wrong time to datetime conversion using ODBC - who am I to blame?

    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 (!).

    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" wrote in message
    news:u7GxYIZ%23IHA.5040@TK2MSFTNGP04.phx.gbl...
    >
    > "Jeff Boyce" skrev i en meddelelse
    > news:egNH7MY%23IHA.5012@TK2MSFTNGP03.phx.gbl...
    >> 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.
    >
    >> More info, please...
    >>
    >> Regards
    >>
    >> Jeff Boyce
    >> Microsoft Office/Access MVP

    >
    >




  5. Re: Wrong time to datetime conversion using ODBC - who am I to blame?


    "Jeff Boyce" skrev i en meddelelse
    news:%23BB%23qRZ%23IHA.5012@TK2MSFTNGP03.phx.gbl.. .
    >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 (!).


    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!

    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP
    >




  6. Re: Wrong time to datetime conversion using ODBC - who am I to blame?

    Not that I'm aware of...

    Jeff

    "Kaj Julius" wrote in message
    news:O9nfTZy%23IHA.5192@TK2MSFTNGP04.phx.gbl...
    >
    > "Jeff Boyce" skrev i en meddelelse
    > news:%23BB%23qRZ%23IHA.5012@TK2MSFTNGP03.phx.gbl.. .
    >>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 (!).

    >
    > 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!
    >
    >> Regards
    >>
    >> Jeff Boyce
    >> Microsoft Office/Access MVP
    >>

    >
    >




+ Reply to Thread