methew / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

Date columns are returned as strings instead of dates #353

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
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 

Original issue reported on code.google.com by jep...@gmail.com on 14 Jan 2014 at 10:18

GoogleCodeExporter commented 9 years ago
I use this constantly so something is definitely wrong.  In fact, can you run 
the SQL Server unit tests in tests3/sqlserver.py?

Original comment by mich...@kleehammer.com on 3 Feb 2014 at 1:12

GoogleCodeExporter commented 9 years ago
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)

Original comment by jep...@gmail.com on 3 Feb 2014 at 5:34