This is an archived copy of the forum for reference purposes

How can I select records with a date by month and year? (MySQL)

posted by markashleybell 8 years ago

The following SQL retrieves the correct rows:

SELECT * 
FROM EVENTS WHERE MONTH( event_date ) = 11
AND YEAR( event_date ) = 2011

However, there doesn't seem to be a way to perform this kind of query using fActiveRecord; there are no suitable operators or functions to select by part of a date in Flourish SQL either.

How can I retrieve only the records where month equals X and year equals X? Any help gratefully received.

You can use any DB-specific SQL in fRecordSet::buildFromSQL(), I just can't confirm that it works across databases.

In terms of doing this with fRecordSet, I would probably do the following:

$set = fRecordSet::build(
    'Event',
    array(
        'event_date>=' => '2011-11-01',
        'event_date<=' => '2011-11-31'
    )
);

You can make the month number dynamic by using fDate::modify().

$month = 10;
$start = new fDate('2011-' . $month . '-01');
$set = fRecordSet::build(
    'Event',
    array(
        'event_date>=' => $start,
        'event_date<=' => $start->modify('Y-m-t')
    )
);

posted by wbond 8 years ago

Thanks for your response, that's most helpful. I didn't realise you could use raw db-specific SQL; I thought I saw it described as 'Flourish SQL' somewhere, which made me assume it was a subset.

I ended up doing this (before I read your reply) which also works:

$year = 2011;
$month = 11;

$from = new fDate($year . "-" . $month . "-1");
$to = $from->adjust('+1 month');

$set = fRecordSet::build(
	'Event',
	array('event_date>=' => $from, 'event_date<' => $to, 'publish=' => 1),
    array('event_date' => 'asc')
);

Thanks for releasing this excellent framework!

posted by markashleybell 8 years ago