Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
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,342 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:
- $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:
- /**
- * 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
- * Motivation: create a database independate BETWEEN clause
- *
- * @param $options array
- * @return array start and ending points
- */
- 'field' => $this->name.'.created',
- 'start' => 'now',
- 'end' => '-1 week',
- 'type' => false, //date, time, datetime, false => use column type
- );
- $options = am($default_options, $options);
- //load in DB connection config, to get datetime format from column
- $db =& ConnectionManager::getDataSource($this->useDbConfig);
- if(!$options['type']) {
- } else {
- }
- } else {
- }
- return $return;
- }
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
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!