cast()ing a DateTime from SQLite throws exception, works in PostgreSQL

[Former user]

Something such as

session.query(cast(func.min(test_table.c.sometime), DateTime)).one()

Will cause an exception to be thrown when using SQLite but not PostgreSQL. If the cast is removed, it works fine.

Attached is a short script which reproduces the problem.

[Former user]

bug reproduction script

[Former user]

Forgot to add myself to CC:, could someone please add [e-mail] when they get a chance?

[Mike Bayer]

this is a limitation of SQLite. SQLite has no actual "DATETIME" type. your MIN() + CAST has the effect of returning an integer ("2010" specifically) which fails SQLAlchemy's DateTime processor which expects a string. there is nothing SQLAlchemy can do about this limitation.

Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(sometime DATETIME);
sqlite> insert into test (sometime) values ('2010-01-01 00:00:00.000000');
sqlite> SELECT CAST(min(test.sometime) AS DATETIME) AS anon_1 FROM test;
2010
sqlite>

[Mike Bayer]

Removing milestone: 0.6.xx (automated comment)