Specify date in WHERE clause

6 posts by 2 authors in: Forums > CMS Builder
Last Post: February 3, 2010   (RSS)

By terryally - January 31, 2010

I have a date field and I want to specify a month in the WHERE clause. How do I do this?

Re: [terryally] Specify date in WHERE clause

By Chris - February 2, 2010

Hi terryally,

If your date field is called "date", and you want to list only records from December, 2009, you can use this code:

'where' => "MONTH(date) = 12 AND YEAR(date) = 2009"

I hope this helps! Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Specify date in WHERE clause

By terryally - February 3, 2010

Hi Chris,

This has worked well! Exactly what I am looking for.

There is a slight variation that I want. I want to list only today's and future events and I am achieving that through the following in the select statement:

'where' => 'start_date >= NOW()',

That works fine. I am getting a long list of current and future events. I now want to segment in month blocks so, I've added your solution which works absolutely fine:

'where' => 'start_date >= NOW() AND MONTH(start_date) = 2',


However what I cannot achieve is a single header for each month. I need to find a way to include the following

<?php echo date("F", strtotime($showsRecord['start_date'])) ?>

before the loop starts so it appears only once.


How do I do that?

Re: [terryally] Specify date in WHERE clause

By Chris - February 3, 2010

Hi terryally,

That's a good approach, yes. You'll want to test to make sure your code fails gracefully if no records match (you could test this by adding " AND 0 = 1" to the end of your where clause.)

Specifically, I think you may need to replace this line:

<?php if($showsRecord['start_date']): ?>

...with this:

<?php if($showsRecord): ?>

Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Specify date in WHERE clause

By terryally - February 3, 2010

Yes, the graceful failure test worked.

I've also changed the code as advised and it provides the same result as with the previous code.

Thanks for your very efficient and effective help.