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:  https://blogs.msdn.com/b/brian_swan/archive/tags/connection+options/.

Thanks.

-Brian

Share this on Twitter

Comments

• Anonymous
  February 08, 2011
  I'm glad that option exists, but if you want a string why not case it as such in your query/sproc. I'd be curious to hear from people who actually would use this option. Is it that the mssql_ and odbc_ drivers returned DataTime columns strings.
• Anonymous
  February 08, 2011
  Casting datetime columns to nvarchar would be another option here.The reason the ReturnDatesAsStrings option exists in the driver is that we received overwhelming feedback from the PHP community that datetime columns should be returned as strings (without having to alter SQL statements). To maintain backwards compatibility with the 1.0 version of the driver, the ReturnDatesAsStrings option was added in the 1.1 version.I also would be interested in hearing from people who are using the ReturnDatesAsStrings option.-Brian
• Anonymous
  February 09, 2011
  My issue with DateTime is not being able (unless I just haven't found the right way) to set a default format and it not having a __toString method. What I'd like to do, is tell the sqlsrv driver to not use the DateTime class by default for a datatime column, instead I'd like to say, use a My_DateTime instance for any datetime column. That way, I can add a __toString and a default format to keep things consistent throughout my app.
• Anonymous
  February 09, 2011
  @Joey Rivera-I don't know of a way to do what you are looking for...not exaclty anyway.You can specify the PHP type for a field when you retrieve a row of data: msdn.microsoft.com/.../cc296208(SQL.90).aspxAnother possibilty might be to use the sqlsrv_fetch_object function: msdn.microsoft.com/.../cc626308(SQL.90).aspx. You might be able to define logic within a class that automatically does the conversion you are looking for when calling this function (although I haven't tried this myself). If you do try this approach, I'd be interested in understanding how it goes.-Brian