If you work with PHP and MSSQL as Database Backend you probably ran into the same issue like I did. The “Conversion failed when converting DATETIME from character string” error message.
- Autor
- Alexander Troy
- Datum
- 9. April 2013
- Lesedauer
- 2 Minuten
The problem
The implicit conversion of a string/nvarchar to Datetime in MSSQL can be tricky because it depends on your server’s locale settings and the right format of your string. To be sure the right query is generated use a standardized format like ISO-8601 on both sides. But be carefull MSSQL processes ISO-8601 strings like “2013-04-03T07:08:22+0000” not correctly and you get an error.
Example
There is a PHP constant to convert to an ISO8601 Date - cool things ha!
$timezone = new DateTimeZone("UTC");
$date = new DateTime("now", $timezone);
echo $date->format(DateTime::ISO8601);
This will give you something like this “2013-04-03T07:08:22+0000”. But this leads to this nasty error. So my first thought was “ok, PHP as always..”, but with a little bit of investigation it turns out that PHP follows the standard.
The solution
1. The “right” PHP format
The problem is the timezone definition and its correct handling, so we convert the PHP time to UTC and remove the definition. You don’t need to set a timezone, if your server is configured to UTC correctly.
$timezone = new DateTimeZone("UTC");
$date = new DateTime("now", $timezone);
echo $date->format("Y-m-d\TH:i:s");
2. Use CONVERT in your SQL Query
That both worlds match without errors use CONVERT and the same format (126 = ISO-8601). For more datetime convert formats visit this page.
SELECT * FROM Alert
WHERE (CreatedOn < CONVERT(datetime, '2013-04-03T07:08:22', 126));
Conclusion
- Do not trust implicit conversions
- Use defined standards
- Test even if you follow them
