pyodbc returns SQL Server DATE fields as strings

Question

I'm using pyodbc to query a SQL Server 2008 database table with columns of DATE type.

The resulting rows of data contain date strings rather than python datetime.date or datetime.datetime instances.

This only appears to be an issue for columns of type DATE; columns of type DATETIME are handled correctly and return a datetime.datetime instance.

Example

import pyodbc
from pprint import pformat
db = pyodbc.connect("DRIVER={SQL Server};SERVER=.\\SQLEXPRESS;DATABASE=scratch;Trusted_Connection=yes")
print pformat(db.cursor().execute("select * from Contract").description)

Results:

(('id', <type 'int'>, None, 10, 10, 0, False),
 ('name', <type 'str'>, None, 23, 23, 0, False),
 ('some_date', <type 'unicode'>, None, 10, 10, 0, True),
 ('write_time', <type 'datetime.datetime'>, None, 23, 23, 3, False))

Note that the some_date column is indicated as type unicode string, however, in the database this column is defined as DATE:

CREATE TABLE dbo.Contract(
    id INT NOT NULL,
    name VARCHAR(23) NOT NULL,
    some_date DATE NULL,
    write_time DATETIME NOT NULL)

Is this normal, and how can I best correct it?

Answer 1

Use the SQL Server native client. e.g. Put Driver={SQL Server Native Client 10.0} in your connection string,instead of DRIVER={SQL Server}.

Reproduced your scenario with date being returned as string using SQL Server ODBC driver. When using a 2008+ compatible version of the SQL Server native client, the date type is returned as expected, but it looks like datetime2 gets returned as string (in my limited testing).

Table definition:

create table dbo.datetest (
    [date] date not null,
    [datetime] datetime not null,
    [datetime2] datetime2 not null
);

insert into
    dbo.datetest
values
    (CAST(current_timestamp as DATE),
     CAST(current_timestamp as datetime),
     CAST(current_timestamp as datetime2));

Example:

import pyodbc
from pprint import pformat
db = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                    server='TESTSRVR', database='TESTDB',
                    trusted_connection='yes')
print pformat(db.cursor().execute("select * from dbo.datetest").description)

Results:

(('date', <type 'datetime.date'>, None, 10, 10, 0, False),
 ('datetime', <type 'datetime.datetime'>, None, 23, 23, 3, False),
 ('datetime2', <type 'unicode'>, None, 27, 27, 0, False))