in MSSQL, smalldatetime is less precise than standard datetime field.
Obviously this table wasn't created by web2py.
Anyway, the problem here is that your "now()" - that is not a function
provided by web2py - is set to a too much "resoluted" (as in "too much
numbers after the seconds part) value.
That date goes into the microsecond detail, and smalldatetime "accepts" an
internal conversion from string to a smalldatetime value only if you
specify up to milliseconds (i.e. 2000-01-01 00:00:00.001 vs 2000-01-01
00:00:00.000001).
You need to "round" the value returned by your "now()" function.
On Wednesday, September 19, 2012 6:46:16 PM UTC+2, KMax wrote:
>
> Hello,
> DAL with MSSQL
> Query like:
> db(db.mytable.date>now()).select()
> Gives error:
> <class 'pyodbc.DataError'> ('22007', '[22007] [FreeTDS][SQL
> Server]Conversion failed when converting character string to smalldatetime
> data type. (295) (SQLExecDirectW)')
>
> now() -> translated into:
> '2012-09-19 23:22:56.977817'
>
> Running select using '2012-09-19 23:22:56.977817' from MS SQL Studio
> gives same error,
> BUT
> if I use shorter time:
> '2012-09-19 23:22:56.977' - this works.
>
> Please let me know how to workaround this?
> Search gave nothing useful.
>
--
