Interesting gotcha feature with the Microsoft SQL Server extension for PHP on Windows. When querying against a column defined as a datetime, the native PHP SQL Server extension returns a string where as the Microsoft extension returns a DateTime object. So if you are expecting a string, you’ll need to adjust your code accordingly.

I personally like to have my dates and times as timestamps but you can rework this for your needs. I could see extending this return a formatting date string instead of a timestamp or perhaps include a date format parameter to the function.

public function date_normalizer($d){
	if($d instanceof DateTime){
		return $d->getTimestamp();
	} else {
		return strtotime($d);
	}
}

Another route is to pass in the connection parameters necessary to make the conversion at the driver level. This is done by adding the “ReturnDatesAsStrings” parameter to your connection parameters.

$connectionParams = array(
	'USR'=>'user',
	'PASS'=>'pass',
	'Database'='myDatabase',
	'ReturnDatesAsStrings'=>true,
	'Timeout'=>1,
);
$conn = sqlsrv_connect('127.0.0.1',$connectionParams);

You should follow me on Twitter.

Tags: datetime, gotcha, mssql, php, sql server, sqlsrv

This entry was posted on Saturday, March 13th, 2010 at 9:37 am and is filed under code, mssql, php. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.