Python ADO Date Time database fields

[goldtech]

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

 

[Mike Driscoll]

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

 

[John Machin]

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:0.12344907407407407

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

HTH,
John

 

[goldtech]

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

 

[Mike Driscoll]

snip







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

 

[John Machin]

snip







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>