Python ADO Date Time database fields

Discussion in 'Python' started by goldtech, Jan 24, 2008.

  1. goldtech

    goldtech Guest

    Hi,

    Given an MS-Access table with a date type field with a value of:
    12:00:00 AM - just"12:00:00 AM", there's nothing else in the field.

    I want to print exactly what's in the field, ie. "12:00:00 AM". What I
    get printed is: 12/30/0/ 00:00:00

    I try:

    import win32com.client
    from win32.client import Dispatch

    oConn=Dispatch('ADODB.Connection')
    db = r'C:\mydb.mdb'
    oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0; data Source="+db)
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    c = oConn.OpenSchema(20)

    while not c.EOF:
    tn = c.Fields.Item('Table_Name').Value
    oRS.Open(tn)
    (oRS, dt) = oConn.Execute('SELECT date_field FROM '+tn+' GROUP BY
    date_field')
    while not oRS.EOF:
    print oRS.Fields(dt).Value # print here
    oRS.MoveNext()
    c.MoveNext()

    oRS.Close()
    oConn.Close()
    # end

    What I get printed is: 12/30/0/ 00:00:00

    What do I to get exactly what's in the field?

    Thanks,

    Lee G
     
    goldtech, Jan 24, 2008
    #1
    1. Advertising

  2. On Jan 24, 5:55 pm, goldtech <> wrote:
    > Hi,
    >
    > Given an MS-Access table with a date type field with a value of:
    > 12:00:00 AM - just"12:00:00 AM", there's nothing else in the field.
    >
    > I want to print exactly what's in the field, ie. "12:00:00 AM". What I
    > get printed is: 12/30/0/ 00:00:00
    >
    > I try:
    >
    > import win32com.client
    > from win32.client import Dispatch
    >
    > oConn=Dispatch('ADODB.Connection')
    > db = r'C:\mydb.mdb'
    > oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0; data Source="+db)
    > oRS = Dispatch('ADODB.RecordSet')
    > oRS.ActiveConnection = oConn
    > c = oConn.OpenSchema(20)
    >
    > while not c.EOF:
    > tn = c.Fields.Item('Table_Name').Value
    > oRS.Open(tn)
    > (oRS, dt) = oConn.Execute('SELECT date_field FROM '+tn+' GROUP BY
    > date_field')
    > while not oRS.EOF:
    > print oRS.Fields(dt).Value # print here
    > oRS.MoveNext()
    > c.MoveNext()
    >
    > oRS.Close()
    > oConn.Close()
    > # end
    >
    > What I get printed is: 12/30/0/ 00:00:00
    >
    > What do I to get exactly what's in the field?
    >
    > Thanks,
    >
    > Lee G


    I don't know for sure, so I practiced my Google-Fu and found this
    recipe which might be of use to you:

    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/52267

    I also found this script, which might help you interface better with
    Access:

    http://www.ecp.cc/pyado.html

    And then there just connecting to it with the adodb module:

    http://phplens.com/lens/adodb/adodb-py-docs.htm

    Mike
     
    Mike Driscoll, Jan 25, 2008
    #2
    1. Advertising

  3. goldtech

    John Machin Guest

    On Jan 25, 10:55 am, goldtech <> wrote:
    > Hi,
    >
    > Given an MS-Access table with a date type field with a value of:
    > 12:00:00 AM - just"12:00:00 AM", there's nothing else in the field.
    >
    > I want to print exactly what's in the field, ie. "12:00:00 AM". What I
    > get printed is: 12/30/0/ 00:00:00
    >
    > I try:

    [snip]
    > print oRS.Fields(dt).Value # print here


    try this:

    val = oRS.Fields(dt).Value
    print type(val)
    print float(val)

    If the last one gives you 0.0, then you have got exactly what's in the
    database -- stored as a fraction of a day. Six AM would give you 0.25.

    Converting that to 24 hour clock is easy:
    >>> val = 0.12345
    >>> seconds = int(round(val * 60 * 60 * 24))
    >>> minutes, second = divmod(seconds, 60)
    >>> hour, minute = divmod(minutes, 60)
    >>> '%02d:%02d:%02d' % (hour, minute, second)

    '02:57:46'
    >>> ((((46/60.)+57)/60.)+2)/24. # checking

    0.12344907407407407

    If you don't get 0.0, let us know what you did get.

    HTH,
    John
     
    John Machin, Jan 25, 2008
    #3
  4. goldtech

    goldtech Guest

    snip
    >
    > try this:
    >
    > val = oRS.Fields(dt).Value
    > print type(val)


    this gives: <type 'time'>



    > print float(val)


    yes, it gives 0.0

    But there should be a way to print what is *actually in the field*.
    When I open the DB table in Access I see: 12:00:00 AM.

    That's what I want - the value, and the form of the value, exactly as
    seen in the field...

    As an aside, the roughly eqivalent code in Perl will print the
    "12:00:00 AM" - (the trick for the date types in Perl is to add: "use
    Win32::OLE::Variant;"

    There has to be a way:^)

    snip
     
    goldtech, Jan 25, 2008
    #4
  5. On Jan 25, 7:48 am, goldtech <> wrote:
    > snip
    >
    >
    >
    > > try this:

    >
    > > val = oRS.Fields(dt).Value
    > > print type(val)

    >
    > this gives: <type 'time'>
    >
    > > print float(val)

    >
    > yes, it gives 0.0
    >
    > But there should be a way to print what is *actually in the field*.
    > When I open the DB table in Access I see: 12:00:00 AM.
    >
    > That's what I want - the value, and the form of the value, exactly as
    > seen in the field...
    >
    > As an aside, the roughly eqivalent code in Perl will print the
    > "12:00:00 AM" - (the trick for the date types in Perl is to add: "use
    > Win32::OLE::Variant;"
    >
    > There has to be a way:^)
    >
    > snip


    You could try posting to the PyWin32 group too. They would probably
    know.

    http://mail.python.org/mailman/listinfo/python-win32

    Mike
     
    Mike Driscoll, Jan 25, 2008
    #5
  6. goldtech

    John Machin Guest

    On Jan 26, 12:48 am, goldtech <> wrote:
    > snip
    >
    >
    >
    > > try this:

    >
    > > val = oRS.Fields(dt).Value
    > > print type(val)

    >
    > this gives: <type 'time'>
    >
    > > print float(val)

    >
    > yes, it gives 0.0
    >
    > But there should be a way to print what is *actually in the field*.


    What is "actually in the field" is a bunch of bits -- in this case all
    zero. What you *see* is quite another matter.

    > When I open the DB table in Access I see: 12:00:00 AM.


    And if you were to close Access, go Start / Control Panel / Regional
    and Language settings, change the Time display format to HH:mm:ss, and
    go back to look at your database, you'd see 00:00:00 ... send a copy
    of your database to someone in another country and they'd likely see
    something else again.

    >
    > That's what I want - the value, and the form of the value, exactly as
    > seen in the field...


    >
    > As an aside, the roughly eqivalent code in Perl will print the
    > "12:00:00 AM" - (the trick for the date types in Perl is to add: "use
    > Win32::OLE::Variant;"
    >
    > There has to be a way:^)


    C:\junk>type goldtech.py
    def time_format_12(day_fraction):
    assert 0.0 <= day_fraction < 1.0
    seconds = int(day_fraction * 60 * 60 * 24)
    minutes, second = divmod(seconds, 60)
    hour, minute = divmod(minutes, 60)
    if hour >= 12:
    tag = 'PM'
    else:
    tag = 'AM'
    hour12 = (hour - 1) % 12 + 1
    return '%02d:%02d:%02d %s' % (hour12, minute, second, tag)

    if __name__ == "__main__":
    import sys
    args = sys.argv[1:]
    if args:
    tests = map(float, args)
    else:
    tests = (
    [0.0, 0.5, 0.9999999]
    + [(h + 0.99) / 24.0 for h in (0, 1, 11, 12, 13, 23)]
    )
    for test in tests:
    print "%8.6f %s" % (test, time_format_12(test))

    C:\junk>goldtech.py
    0.000000 12:00:00 AM
    0.500000 12:00:00 PM
    1.000000 11:59:59 PM
    0.041250 12:59:24 AM
    0.082917 01:59:24 AM
    0.499583 11:59:23 AM
    0.541250 12:59:24 PM
    0.582917 01:59:24 PM
    0.999583 11:59:23 PM

    C:\junk>goldtech.py 0.1 0.01 0.001
    0.100000 02:24:00 AM
    0.010000 12:14:24 AM
    0.001000 12:01:26 AM

    C:\junk>
     
    John Machin, Jan 25, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    908
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    494
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,346
  4. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,380
    Michael Borgwardt
    May 30, 2004
  5. Navin
    Replies:
    1
    Views:
    770
    Ken Schaefer
    Sep 9, 2003
Loading...

Share This Page