Python 2.3 ODBC Datetime limitations

[Steve Holden]

YuePing Lu wrote:
> Hello,
> 
> Has any of you ever used Python odbc to retrieve data from a relational DB?
> 
> I encountered a problem where it can't handle datetime _earlier than 
> _*1969*, and _later than _*2040*. It just returned some garbage strings 
> when I call str(my_date_object).
> 
> When I call strptime to convert the value I got ValueError exception:
> 
> # Table records
>    for record in self._records:
>     columns = []
>     for i in range(len(self._description)):
>      if self._description[i][1]=='DATE':
>       strDate=str(record[i])
>       try:
>        #convert the date format to be mm/dd/yyyy:
>        oDate=time.strptime(strDate) #convert to struct_time object
>        strfDate=time.strftime("%m/%d/%Y", oDate) 
> 
> columns.append(strfDate)
>       except ValueError, msg:
>        raise "Date converstion error", msg
> 
> ...
>        Any other solutions that you've heard?
> 
> Thanks very much in advance,
> 
Reading the documentation for the time module, you will see:


The epoch is the point where the time starts. On January 1st of that 
year, at 0 hours, the ``time since the epoch'' is zero. For Unix, the 
epoch is 1970. To find out what the epoch is, look at gmtime(0).

The functions in this module do not handle dates and times before the 
epoch or far in the future. The cut-off point in the future is 
determined by the C library; for Unix, it is typically in 2038.


So this may not be a problem with the odbc module, which isn't the most 
up-to-date piece of software on the block but is mostly reliable for 
simple purposes.

I'd try something from the datetime module. When I retrieve a date 
column from a postgreSQL database using ODBC is see:

 >>> curs.execute("SELECT orginvdt FROM organization WHERE orginvdt IS 
NOT NULL")
0
 >>> row = curs.fetchone()
 >>> row
(<DbiDate object at 0x0099D0A0>,)
 >>> dbidate = row[0]
 >>> dir(dbidate)
[]

So it isn't easy to know just by looking what you can do with a 
dbitdate. But if you can hack around with the time module you can 
probably hack around with datetime too, and that's a more capable 
module. However it's possible that the epoch-to-2038 restriction is 
actually imposed byt he odbc drivers, in which case you will need to 
look for another Python driver module for your database.

It would have been helpful if you hadn't trapped the exception so that 
we could have seen the exact traceback - this would have given man more 
clues as to what was going on.

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC/Ltd           http://www.holdenweb.com
Skype: holdenweb      http://del.icio.us/steve.holden
------------------ Asciimercial ---------------------
Get on the web: Blog, lens and tag your way to fame!!
holdenweb.blogspot.com        squidoo.com/pythonology
tagged items:         del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------