Planet CakePHP

I've been using CakePHP 1.2.x for projects at work, where our primary database back-end is Microsoft SQL Server. Maybe I'm just missing something, but it seems to me that SQL Server outputs datetime values in an odd way (by odd, I mean not parsable by strtotime). When pulling datetime values out of SQL Server, CakePHP will render them as PHP strings just as they are returned from the server, in this format:
Jan 10 2008 12:25:07:000PM
Now, strtotime parses incoming values according to GNU Date Input Format, which is incompatible with the output from SQL Server (note the milliseconds). To make life easier when working woth models that describe MSSQL tables with datetime fields, using this behavior can make things a little easier.

To use this behavior, put the code in
    app/model/behaviors/mssql_table.php
and including the following line in your model:
var $actsAs = array('MssqlTable' => array());
... and datetime fields will come through as strings formatted according to your current locale (which is parsable by strtotime -- well, at least here in en_us).
Mssql_Table.php
<?php
/**
* $Id: mssql_table.php 127 2007-06-04 13:11:09Z jbenner $
*
* This behavior will automatically handle some of SQL Server's oddities. Right
* now all it does is handle parsing of datetime fields automatically, and
* converts them to GNU datetime compliant strings.
*
* @package pbuapps
* @subpackage pbuapps.base
* @author Joshua Benner
* @copyright 2007 Philadelphia Biblical University
* @version $Revision: 127 $
* @modifiedby $LastChangedBy: jbenner $
* @lastmodified $Date: 2007-06-04 08:11:09 -0500 (Mon, 04 Jun 2007) $
*/
 
/**
* Behavior that will deal with the oddities of SQL Server models.
*/
class MssqlTableBehavior extends ModelBehavior {
 
protected $__defaultParsers = array( 'datetime' => array('parser' => 'parse_datetime'));
 
/**
* Initialize behavior
*
* @param object $model
* @param array $config
*/
function setup(&$model, $config = array()) {
$settings = am(array('parsers' => $this->__defaultParsers), $config);
$this->settings[$model->name] = $settings;
}
 
/**
* After a find is performed, this function will check if any of the fields
* returned are datetime fields and reformat them. This is necessary to
* deal with microsoft's odd datetime output in SQL Server.
*
* @param AppModel $model
* @param array $results
* @param boolean $primary Whether or not this is primary query
*/
function afterFind(&$model, $results, $primary) {
// First: find all the offending fields
$parseFields = array();
// Load parsers from settings
$parsers = $this->settings[$model->name]['parsers'];
// Iterate through fields in table info and check if their type is in our parser list
foreach ($model->_tableInfo->value as $field) {
if (array_key_exists($field['type'], $parsers)) {
// We found a juicy field to parse, so assign a parser function
$parseFields[$field['name']] = $parsers[$field['type']];
}
}
// Now we know which fields to parse, let's parse them!
if (count($parseFields)) {
// Iterate through results rows
foreach ($results as $i=>$r) {
// We don't iterate through models, because they can worry about
// their own datetime parsing if they must.
$fields = $r[$model->name];
// Iterate through parsable fields for each row
foreach ($parseFields as $field=>$func) {
// If this row has this field
if (isset($fields[$field])) {
// Execute associated parser function
$results[$i][$model->name][$field] = $this->$func['parser']($fields[$field]);
}
}
}
}
return $results;
}
 
/**
* Parses the output from MSSQL datetime field and returns it in a format
* compliant with GNU datetime standards (ie: strtotime can parse it).
*
* @param string $datetime
* @return string GNU-compliant date string
*/
protected function parse_datetime($datetime) {
// MSSQL Format: Jan 10 2008 12:25:07:000PM
return strftime('%c', strtotime(str_replace(':000', ' ', $datetime)));
}
}
 
?>