Using Between with Date Ranges in Proper DB Date Format

Posted on 07/09/2009 at 11:56 am by Kevin Wentworth
Viewed 18,028 times | 0 comments

I need my CakePHP apps to run on both LAMP and Windows IIS using MSSQL Server.  Usually this isn't a problem because CakePHP does such a good job of database abstraction and database independence.  However, I came across a scenario the other day where I needed to select a date range using the BETWEEN SQL command.

Using CakePHP's BETWEEN function for date ranges

It's easy, if you only want to use MySQL.  Just do the following (note that the "lesser" or older date comes first):

  1. array('Post.created BETWEEN ? AND ?' => array(date('Y-m-d', strtotime('-1 hour')), date('Y-m-d'));  //BETWEEN is inclusive

Unfortuately, that wasn't going to work for me (or so I thought...more on that later).  MSSQL uses a date format like 'mm/dd/YYYY HH:mm:ss' while MySQL uses the format of 'YYYY-mm-dd HH:mm:ss'.  CakePHP does a great job updating the created and modified columns automagically in your database, so I decided to look under the hood for some pointers. 

Getting date ranges based on DB date format

This is the solution I've developed.  I wanted to call a function that returns an array with the date range (indexed as $range['start'] and $range['end']) and then use those in my $model->find() conditions array.  You could easily combine the 2 calls into 1, but I prefer and needed the flexibity of 2 calls.

In your controller PHP file:

  1. $range = $this->dateRangeConditions(array('end'=>'-1 hour'));
  2. $tries = $this->find('all', array('conditions'=>array('created BETWEEN ? and ?'=>array($range['start'], $range['end'])), 'recursive'=>-1));

To make the dateRangeConditions() function, I used the dbconfig settings to open an instance of the connection and get the formats for each type of data.

In app_model.php I created the dateRangeConditions() function:

  1. /**
  2.  * Returns the start and end date for a date range.  Will use the format of the column unless $options['type'] is set to time, date, datetime
  3.  * Motivation: create a database independate BETWEEN clause
  4.  *
  5.  * @param $options array
  6.  * @return array start and ending points
  7.  */
  8. function dateRangeConditions($options = array()) {
  9.     $default_options = array(
  10.                 'field' => $this->name.'.created',
  11.                 'start' => 'now',
  12.                 'end' => '-1 week',
  13.                 'type' => false, //date, time, datetime, false => use column type
  14.                 );
  15.     $options = am($default_options, $options);
  17.     //load in DB connection config, to get datetime format from column
  18.     $db =& ConnectionManager::getDataSource($this->useDbConfig);
  19.     $default = array('formatter' => 'date');
  21.     if(!$options['type']) {
  22.         $colType = array_merge($default, $db->columns[$this->getColumnType($options['field'])]);   
  23.     } else {
  24.         $colType = array_merge($default, $db->columns[$options['type']]);
  25.     }
  27.     $return = array();
  28.     if (!array_key_exists('format', $colType)) {
  29.         $return['start'] = strtotime($options['end']);  //timestamp
  30.         $return['end'] = strtotime($options['start']);  //timestamp
  31.     } else {
  32.         $return['start'] = $colType['formatter']($colType['format'], strtotime($options['end']));
  33.         $return['end'] = $colType['formatter']($colType['format'], strtotime($options['start']));
  34.     }
  35.     return $return;
  37. }

As you can see, this will automatically format and return an array with 2 indexes, start and end, properly formatted for the database context you are using.

Now, about that whole concept of needing to use a different date format for MSSQL server inserts and updates.  I wrote my code on my MySQL development environment and then ported it over to the MSSQL environment.  And guess what, the format in the query was the same as on the MySQL query.  I was using MSSQL 2005 so maybe M$ has improved SQL server, but [I thought] I remembered MSSQL 2000 needing the proper date format, with slashes, not hyphens.  I'll be the first to admit my DBA skills on MSSQL server are weak, so I'm curious to find out more about date formats on MSSQL 2000 vs. 2005.

A method for getting NOW() based on the DB

I found this post on my quest, it wasn't quite what I wanted, but a great way to get NOW() using the correct database SQL command.


-Kevin Wentworth

Bookmark and Share

Tags for Using Between with Date Ranges in Proper DB Date Format

Cakephp | Database | Mssql | Mysql | Web Programming

Comments for this Posting

No comments. Be the first to post a reply.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design