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

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: [terryally] Specify date in WHERE clause

Dear Chris,

I think I found a solution. Do you foresee any problems that this might throw up? I just replicate this for each month of the year.

<?php
list($showsRecords, $showsMetaData) = getRecords(array(
'tableName' => 'shows',
'where' => 'start_date >= NOW() AND MONTH(start_date) = 2',
'orderBy' => 'start_date ASC',
));
$showsRecord = @$showsRecords[0]; // get first record
?>
<?php if($showsRecord['start_date']): ?>
<h3><?php echo date("F", strtotime($showsRecord['start_date'])) ?></h3>
<?php foreach ($showsRecords as $record): ?>
<p>
<b><?php echo date("D, M jS Y", strtotime($record['start_date'])) ?></b><br/>
<b><a href="<?php echo $record['_link'] ?>" title="<?php echo $record['event_title'] ?>"><?php echo $record['event_title'] ?></a></b>
</p>
<?php endforeach ?>
<?php endif ?>


Regards

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

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.