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?

I have just tried it in Python 2.6 with pyodbc and MSSQL 2008 - it gives me a datetime object. — Artsiom Rudzenka, Aug 24 '11 at 08:47
@Artsiom Yes, this problem seems to only affect DATE columns. — Daniel Fortunov, Aug 24 '11 at 09:00
I can't check this with DATE column but http://code.google.com/p/pyodbc/wiki/DataTypes tells that all should be ok. Anyway you can try to use datetime.strptime method to convert string to datetime object if needed. — Artsiom Rudzenka, Aug 24 '11 at 09:07
I am also getting this issue. Again, specifically with DATE, not DATETIME. — Robino, Mar 31 '17 at 11:05

Bryan · Accepted Answer · 2013-01-14T16:35:54.453

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

Excellent... now, I just need to work out how to do this under Linux, where I'm using unixODBC and freeTDS... Looks like freeTDS doesn't support anything over SQL 2005: http://www.freetds.org/userguide/tdshistory.htm — Daniel Fortunov, Aug 26 '11 at 08:39
Newer versions of FreeTDS do support date and datetime: FreeTDS 0.95 or newer does (check out https://github.com/FreeTDS/freetds) — Jakub P., Aug 26 '16 at 09:37

pyodbc returns SQL Server DATE fields as strings

Example

1 Answers1

Linked