| Issue 5: | Support SQL Server 2008's new DATE related data types | |
| Back to list |
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
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
(No comment was entered for this change.)
Status:
Investigating
Dec 1, 2008
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
(No comment was entered for this change.)
Status:
Invalid
Nov 21, 2010
(No comment was entered for this change.)
Status:
NoFix
Dec 21, 2010
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
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?
|
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?