Support SQL Server 2008's new DATE related data types

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by ivan.mel...@gmail.com on 30 Nov 2008 at 9:41

[GoogleCodeExporter]

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:
http://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?

Original comment by mkleehammer on 1 Dec 2008 at 3:38

[GoogleCodeExporter]

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?

Original comment by mkleehammer on 1 Dec 2008 at 3:42

[GoogleCodeExporter]

Original comment by mkleehammer on 1 Dec 2008 at 3:45

• Changed state: Investigating

[GoogleCodeExporter]

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.

Original comment by ivan.mel...@gmail.com on 1 Dec 2008 at 8:04

[GoogleCodeExporter]

Original comment by mkleehammer on 1 Dec 2008 at 8:14

• Changed state: Invalid

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:43

• Changed state: NoFix

[GoogleCodeExporter]

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.

Original comment by pedri...@gmail.com on 21 Dec 2010 at 10:27

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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?

Original comment by andrzej....@gmail.com on 29 Sep 2013 at 9:20