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

