| Issue 353: | Date columns are returned as strings instead of dates | |
| 1 person starred this issue and may be notified of changes. | Back to list |
When selecting a column of DATE type in SQL Server, the result is returned as str instead of datetime.date. For example, this is the output I get:
>>> cursor = conn.cursor()
>>> cursor.execute("""
SET NOCOUNT ON
DECLARE @temp TABLE ( DateColumn date)
INSERT INTO @temp (DateColumn) VALUES ('01-13-2014')
SELECT DateColumn, dateadd(dd,1, DateColumn), datediff(dd, DateColumn, getdate()) FROM @temp
""")
>>> cursor.description
(('DateColumn', builtins.str, None, 10, 10, 0, True),
('', builtins.str, None, 10, 10, 0, True),
('', builtins.int, None, 10, 10, 0, True))
>>> cursor.fetchall()
[('2014-01-13', '2014-01-14', 1)]
My database is using SQL Server 2008. I've tried the above code using Python 3.3, pyodbc 3.07, on both Windows and Ubuntu using freetds
Feb 2, 2014
Project Member
#1
mich...@kleehammer.com
Status:
Investigating
Feb 3, 2014
Here is my output:
Did not find the pyodbc library in the build directory. Will use an installed version.
python: 3.3.3 (default, Nov 19 2013, 16:43:19)
[GCC 4.6.3]
pyodbc: 3.0.7 /home/jdo/env/lib/python3.3/site-packages/pyodbc.cpython-33m.so
odbc: 03.52
driver: libtdsodbc.so 0.91
supports ODBC version 03.50
os: Linux
unicode: Py_Unicode=4 SQLWCHAR=2
======================================================================
ERROR: test_binary_null (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 304, in test_binary_null
self._test_strtype('varbinary', None, colsize=100)
File "sqlservertests.py", line 199, in _test_strtype
self.cursor.execute("insert into t1 values(?)", value)
pyodbc.DataError: ('22018', '[22018] [FreeTDS][SQL Server]Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query. (257) (SQLExecDirectW)')
======================================================================
ERROR: test_date (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 534, in test_date
self.cursor.execute("insert into t1 values (?)", value)
pyodbc.Error: ('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)')
======================================================================
ERROR: test_drivers (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 1256, in test_drivers
drivers = pyodbc.drivers()
AttributeError: 'module' object has no attribute 'drivers'
======================================================================
ERROR: test_none_param (__main__.SqlServerTestCase)
Ensure None can be used for params other than the first
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 1136, in test_none_param
self.cursor.execute("update t1 set n=?, blob=?", 2, None)
pyodbc.DataError: ('22018', '[22018] [FreeTDS][SQL Server]Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. (257) (SQLExecDirectW)')
======================================================================
ERROR: test_time (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 552, in test_time
self.cursor.execute("insert into t1 values (?)", value)
pyodbc.Error: ('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)')
======================================================================
FAIL: test_datetime2 (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 566, in test_datetime2
self.assertEquals(value, result)
AssertionError: datetime.datetime(2007, 1, 15, 3, 4, 5) != '2007-01-15 03:04:05.0000000'
======================================================================
FAIL: test_rowcount_reset (__main__.SqlServerTestCase)
Ensure rowcount is reset to -1
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 772, in test_rowcount_reset
self.assertEquals(self.cursor.rowcount, -1)
AssertionError: 0 != -1
----------------------------------------------------------------------
Ran 190 tests in 6.235s
FAILED (failures=2, errors=5)
|