MainelyDesign.com Blog

Using Between with Date Ranges in Proper DB Date Format

Posted on 07/09/2009 at 11:56 am by Kevin Wentworth
Viewed 18,371 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):

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:

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:

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.

Cheers,

-Kevin Wentworth