PHP datetime and MSSQL

Dev Diary

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.

Author
Alexander Troy
Date
April 9, 2013
Reading time
2 Minutes

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

More of that?

Fusonic-Linq Write less do more!
Dev Diary
Fusonic-Linq: Write less do more!
April 4, 2013 | 4 Min.

Contact form

*Required field
*Required field
*Required field
*Required field
We protect your privacy

We keep your personal data safe and do not share it with third parties. You can find out more about this in our privacy policy.