#78 adodbapi distinguishing of datetime and date

open-accepted
nobody
None
2
2014-04-22
2010-05-21
Airdrik
No

In MS SQL server, there are two date types: datetime and smalldatetime; datetime has a time component and smalldatetime does not. in adodbapi, they both come in as basically the same type and adodbapi converts them both to python's datetime.date if the time component is 0 or to datetime.datetime otherwise. This leads to possible type differences if a datetime column sometimes has a time component of midnight, and sometimes has other times (for some strange reason datetime.date and datetime.datetime are not comparable).

I would prefer that sql datetime get converted to python datetime.datetime (regardless of if the time component), and sql smalldatetime get converted to python datetime.date (with the exception that sql null get converted to None regardless).
Part of the problem of distinguishing these types is that the type_code of both datetime and smalldatetime is 135 (adDBTimeStamp), however they can be distinguished by the scale field of .description - the scale on datetime is 3, whereas the scale on smalldatetime is 0.

Discussion

  • Vernon Cole
    Vernon Cole
    2010-07-24

    This "feature" has bitten me, too. I like your proposed solution and will investigate using it. -- Vernon

     
  • Vernon Cole
    Vernon Cole
    2010-07-24

    • status: open --> open-accepted
     
  • Vernon Cole
    Vernon Cole
    2010-07-24

    • priority: 5 --> 2
     
  • Vernon Cole
    Vernon Cole
    2014-04-22

    The anti-feature of returning a datetime.date rather than datetime.datetime when the input happened to be exactly at midnight was removed in adodbapi version 2.4 (not long after this suggestion was made). Thank you.
    The suggested switch to datetime.date for smalldatetime fields will not work. There are too many variations of date and datetime fields now since SQL Server 2008 was released.
    I think that this use case might be best handled by providing more variant conversion methods for datetime columns.