Issue 5: Support SQL Server 2008's new DATE related data types
Status:  NoFix
Owner:
Closed:  Dec 2008
Reported by ivan.mel...@gmail.com, Nov 30, 2008
What steps will reproduce the problem?
1. SQL Server 2008 has new data types: DATE, DATETIME2, TIME and
DATETIMEOFFSET.
2. when selecting date for columns of these datatypes, returned values are
all unicode strings:
 * u'2008-01-01'
 * u'2008-01-01 00:00:00.0000000'
 * u'00:00:00.0000000'
 * u'2008-01-01 00:00:00.0000000 +00:00'

What is the expected output? What do you see instead?
 * I would suggest to use python's:
 - datetime.date for DATE
 - datetime.time for TIME
 - datetime.datetime for DATETIME2 and DATETIMEOFFSET


What version of the product are you using? On what operating system?
 * SQL Server 2008 Express, python 2.6 and pyodbc-2.1.1

Please provide any additional information below.

Nov 30, 2008
Project Member #1 mkleehammer
Thanks for the report.

The date, time, and datetime2 are actually already implemented, so there must be
something else.  In fact, we are fortunate enough to have a unit test for date and
time.  I just added datetime2 and it passes.  I'll check it in later.

Let's first run the unit tests on your box.  In the tests directory, run the
sqlserverunittests.py module, passing your connection string.  Since it should have
spaces, you'll need to pass it in quotes.  Here's what I use:

sqlservertests.py "DRIVER={SQL Server Native Client
10.0};SERVER=localhost\SQLEXPRESS;DATABASE=test;Trusted_Connection=yes"

If there is a problem, direct the output to a file and post the results here.

Now, make sure you are using the 'native client' driver.  The "{SQL Server}" driver
does not provide the new 2008 datatypes.  See the Connection Strings wiki page:
https://code.google.com/p/pyodbc/wiki/ConnectionStrings

Assuming that works (and I don't see why it wouldn't), there may be something else
that is converting the values.  Can you provide example code and the output of
Cursor.description so we can see the data types?



Owner: mkleehammer
Nov 30, 2008
Project Member #2 mkleehammer
I forgot to mention that the datetimeoffset is not implemented.  I'm not exactly sure
how I want to handle it since I don't know what to do with the time zone information.

I could ignore the time zone and just return a datetime, and that might be better
than nothing for now.  Ideally, it would be nice if we could get some kind of Python
time zone object, though.

What I really need is to come up with a way for users to configure the data type
conversions.  It's a pretty tough thing to do, however.  How badly do you need this
data type?
Nov 30, 2008
Project Member #3 mkleehammer
(No comment was entered for this change.)
Status: Investigating
Dec 1, 2008
#4 ivan.mel...@gmail.com
You are right, it is something else: I used still "{SQL Server}" driver. Using "{SQL
Server Native Client 10.0}" works like charm (excluding datetimeoffset, which raises
"pyodbc.Error: ('ODBC data type -155 is not supported.  Cannot read column dto.',
'HY000')", but you mentioned this does not work). I do not need "datetimeoffset"
right now.

Thanks for your help.
Dec 1, 2008
Project Member #5 mkleehammer
(No comment was entered for this change.)
Status: Invalid
Nov 21, 2010
Project Member #6 mkleehammer
(No comment was entered for this change.)
Status: NoFix
Dec 21, 2010
#7 pedri...@gmail.com
Out of curiosity (since I'm testing different datatypes in SQL Server 2008 and pyodbc) how difficult would it be to turn a datetimeoffset into a non-naive python datetime object (e.g. the datetime with a tzinfo object).

According to the datetime.tzinfo docs (http://docs.python.org/library/datetime.html#tzinfo-objects) it seems like this could be a class that will know what the UTC offset is, but doesn't specify the DST info (obj.dst() returns None) and leaves other methods like fromutc() unimplemented.  Considering how rinky-dink this SQL type is (no DST info?) this should be enough to support the type and allow anyone to pull out enough info to be useful.

Sep 29, 2013
#9 andrzej....@gmail.com
I'm using SQL Server Native Client 11.0 driver and I get error when I'm trying to retrieve rows from table where column is defined as datetimeoffset.
The error looks like:
"pyodbc.Error: ('ODBC data type -155 is not supported.  Cannot read column dto.',
'HY000')"
is there any possibilities to fix this?