Solving getting bogus dates via MSSQL_QUERY
Heine —Thu, 2012/11/29 - 19:24
Summary: If you get bogus dates back from MSSQL, make sure PHP is compiled against the FreeTDS libary that's currently in use. If not, recompile PHP.
I often work on legacy applications where an interface between Drupal and a database residing on a SQL server are necessary. Due to the nature of legacy projects, this often means working against the 2000 version of the database system.
Recently, an issue occurred on the production server where we save a datetime in the language neutral format YYYYMMDD to prevent issues with LANGUAGE and DATEFORMAT settings (think d-m-Y vs m/d/Y etc). We also use mssql.datetimeconvert set to Off to get dates that can reliably be interpreted by a computer system.
On this particular server, we got really strange results from the SQL server.
The above sample script would result in an obviously bogus datetime:
["computed"]=>
string(19) "1978-03-09 32767:07"
}
Another server operating on the same database, as the same database user would return correct results:
["computed"]=>
string(19) "1978-09-30 00:00:00"
}
This clearly pointed to a factor outside of the database and got us looking into the PHP install on the problematic server. The connection between Drupal and the SQL server is done via the MSSQL PHP driver from the php-sybase package and depends upon the FreeTDS library. The final conclusion of our investigation was that PHP was compiled against a different version of FreeTDS and needed to be recompiled against the current one.
Doing so solved our issues.