FreeTDS and dates
Summary: make sure that FreeTDS is compiled with --enable-msdblib configure option,
or your queries will return wrong dates -- 2009-00-01 instead of 2009-01-01.
There's an obscure problem on Linux/*nix that results in dates shifted back by 1 month.
This behaviour is caused by different dbdatecrack() prototypes in Sybase Open Client
DB-Library/C and the Microsoft SQL DB Library for C. The first one returns month as 0..11
whereas the second gives month as 1..12. See
this FreeTDS mailing list post,
Microsoft manual for dbdatecrack(), and
Sybase manual for dbdatecrack() for details.
FreeTDS, which is used on Linux/*nix to connect to Sybase and MS SQL servers, tries
to imitate both modes:
- default behaviour, when compiled without
--enable-msdblib , gives
dbdatecrack() which is Sybase-compatible,
- when configured with
--enable-msdblib , the dbdatecrack()
function is compatible with MS SQL specs.
pymssql requires MS SQL mode, evidently. Unfortunately at runtime we can't
reliably detect which mode FreeTDS was compiled in (as of FreeTDS 0.63).
Thus at runtime it may turn out that dates are not correct. If there was a way to
detect the setting, pymssql would be able to correct dates on the fly.
If you can do nothing about FreeTDS, there's a workaround. You can redesign your
queries to return string instead of bare date:
SELECT datecolumn
FROM tablename
can be rewritten into:
SELECT
CONVERT(CHAR(10),datecolumn,120)
AS datecolumn FROM tablename
This way SQL will send you string representing the date instead of binary date in
datetime or smalldatetime format, which has to be processed by FreeTDS and pymssql.
On Windows there's no problem at all, because we link with MS library, which
is compatible with SQL Server, obviously.
|