We have a table with DATE column start_on, which is not dehydrated as datetime.date type in the select query as the following:
>>> cur.execute_query('select id, start_on from surveys').fetchone()
[(1, u'2011-02-03'), ]
>>> cur.execute_query('SELECT id, CONVERT(datetime, start_on) from surveys').fetchone()
[(1, datetime.datetime(2011, 2, 3, 0, 0),), ]
Yep, so the problem here is that DATETIME has been supported by FreeTDS for a long time, but DATE and TIME are newer types in SQL Server and Microsoft never added support for them to db-lib and FreeTDS never added support for them either.
There was some discussion of adding it to FreeTDS, but I think that stalled. See this thread:
So we would need to get FreeTDS to support it and then the user would have to make sure to use a very recent FreeTDS (unless pymssql links in said version of FreeTDS).
Because of the FreeTDS limitation, and as I verified with the script below, this also did not work in pymssql 1.0.3:
(pymssql==1.0.3)marca@marca-mac2:~/dev/git-repos/pymssql/stuff$ cat date2.py
from os import getenv
print("pymssql.__version__ = %r" % pymssql.__version__)
server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")
conn = pymssql.connect(
cursor = conn.cursor()
cursor.execute("SELECT * FROM foo")
for row in cursor:
(pymssql==1.0.3)marca@marca-mac2:~/dev/git-repos/pymssql/stuff$ python date2.py
pymssql.__version__ = '1.0.3'
(datetime.datetime(2014, 1, 9, 6, 22, 32, 657000), '2014-01-09', '06:17:00.0000000')
I verify that pymssql 1.x does not support DATE field either, but the SQLAlchemy or Elixir did the right thing for serialization.
The inconsistent behavior I saw is essentially in our code base, which dehydrate the DATE field if the return value is str which is true in pymssql 1.x, in pymssql 2.x the return value type is unicode.
OK, thanks for the update!
I added a section to the FAQ on this issue:
I believe that recent FreeTDS versions (0.95) onwards do support date, time, and datetime2 (see the NEWS file). What now needs to be done for them to be supported in pymssql?