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
– Artsiom Rudzenka, Commented Aug 24, 2011 at 8:47
@Artsiom Yes, this problem seems to only affect DATE columns. — Daniel Fortunov
– Daniel Fortunov, Commented Aug 24, 2011 at 9:00
I can't check this with DATE column but 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
– Artsiom Rudzenka, Commented Aug 24, 2011 at 9:07
I am also getting this issue. Again, specifically with DATE, not DATETIME. — Robino
– Robino, Commented Mar 31, 2017 at 11:05

Bryan · Accepted Answer · 2013-01-14 16:35:54Z

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: freetds.org/userguide/tdshistory.htm
Newer versions of FreeTDS do support date and datetime: FreeTDS 0.95 or newer does (check out github.com/FreeTDS/freetds)

Collectives™ on Stack Overflow

pyodbc returns SQL Server DATE fields as strings

Example

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example

1 Answer 1

2 Comments

Linked

Related