Querying mssql time columns

[Simon Haines]

I think I've found a bug--can anyone else confirm this? It appears that SQLAlchemy cannot query SQLServer time columns because Python datetime.time objects are always promoted to full datetime types. Once promoted the SQLServer returns the error: 'The data types time and datetime are incompatible in the greater than operator. (402) (SQLExecDirectW)'.

The type promotion occurs in sqlalchemy/dialects/mssql/base.py:268-276.

Interestingly, according to the documentation in this file, SQLAlchemy can already determine whether the DATE and TIME types are supported (in SQLServer 2008 or later), and I have confirmed it emits the correct DDL but still treats all bound time values as DATETIMEs anyway. Is this a bug or am I missing something?

Many thanks, Simon.

[Michael Bayer]

On Oct 31, 2011, at 1:18 AM, Simon Haines wrote:

> I think I've found a bug--can anyone else confirm this? It appears that SQLAlchemy cannot query SQLServer time columns because Python datetime.time objects are always promoted to full datetime types. Once promoted the SQLServer returns the error: 'The data types time and datetime are incompatible in the greater than operator. (402) (SQLExecDirectW)'.

>
> The type promotion occurs in sqlalchemy/dialects/mssql/base.py:268-276.

What DBAPI are you using ? The date/time behavior there is all tailored to suit what the DBAPIs want. Also some comparisons aren't possible with SQL server, it has extremely limited date functionality.

Basically SQLAlchemy can only do what the DBAPI allows. As the comments there indicate, Pyodbc crashes if a datetime.time() is passed directly, this might be considered a Pyodbc bug if that behavior can be re-confirmed ,as this code is a few years old. Assuming you're using pyodbc, you'd need to get your query to work as follows first:

import pyodbc
import datetime

conn = pyodbc.connect("dsn=ms_2005;UID=scott;PWD=tiger")

cursor = conn.cursor()

# example:
cursor.execute("SELECT ?", datetime.time(12, 15, 0))
print cursor.fetchall()

# put your statement here using ? for bound parameters
cursor.execute("<your statement goes here>", <your time/etc. parameters go here>)

print cursor.fetchall()

show me a working query with the DBAPI (again assuming pyodbc here) and I will adjust any incompatibilities on the SQLAlchemy side.

[Simon Haines]

Thanks Michael, you're correct I'm using pyodbc with FreeTDS, which doesn't recognize the time datatype (and it seems FreeTDS doesn't support anything above SQLServer 2005). And you're right, this is not a SQLAlchemy issue. Sorry about that. I guess I can work around this by using time.isoformat() in filter constraints. Hope that's portable to windows! Thanks (again) for your help--this is the second time you've helped me out here.

Much appreciated. Simon.