SQL Server Driver for PHP Connection Options: ReturnDatesAsStrings
This is short post to address a frustration I’ve seen mentioned on Twitter and in forums a lot: By default, the SQL Server Driver for PHP returns datetime columns as PHP DateTime objects, not strings. This can be especially frustrating if you are not aware of the ReturnDatesAsStrings connection option. By simply setting this option to 1 (or true) when you connect to the server, datetime columns will be returned as strings.
Note: The PDO_SQLSRV driver always returns datetime columns as strings. There is no flag for automatically returning datetime columns as PHP DateTime objects.
Here’s how to set the ReturnDatesAsStrings option:
// Set ReturnDatesAsStrings to 1 in the connection
// options array that is passed to sqlsrv_connect.
$connectionoptions = array("Database" => $database
, "UID" => $uid
, "PWD" => $pwd
, "ReturnDatesAsStrings" => 1);
$conn = sqlsrv_connect($server, $connectionoptions);
// Insert a new date.
// You can use a DateTime object or a properly formatted
// string when inserting into a SQL Server datetime column.
// e.g. This parameter array also works:
// $params = array(1, '2011-02-08 14:30:15');
$params = array(1, new DateTime());
sqlsrv_query($conn, "INSERT INTO Table_1 (id, date)
VALUES (?,?)", $params);
// Retrieve the inserted date.
// Note that it is returned as a string.
$stmt = sqlsrv_query($conn, "SELECT date FROM Table_1");
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
print("<pre>");
print_r($row['date']);
print("</pre>");
Note that the ReturnDatesAsStrings does not affect how you insert dates into a datetime column: you can use a PHP DateTime object (as shown above), or a properly formatted string (as shown in the comments above). When the code above is executed, the output is a string similar to this:
2011-02-08 14:30:15
Set “ReturnDatesAsStrings” => 0 in the code above (which returns the driver to its default behavior), and the output of the script is similar to this:
DateTime Object
(
[date] => 2011-02-08 14:30:15
[timezone_type] => 3
[timezone] => America/Los_Angeles
)
Hopefully, this will ease the frustration for anyone who uses the driver and is expecting dates to be returned as strings. For more information about connection options in the SQL Server Driver for PHP, see Connection Options in the documentation. For more detail, check out the blog series I’m slowly building up: http://blogs.msdn.com/b/brian_swan/archive/tags/connection+options/.
Thanks.
-Brian