date manipulation question

[IBazz]

Hi,

the date is stored as 2011-02-04

I want to retrieve a record (just one) if that date falls within this month - which it does.

is this AND clause correct or have a I fluked it?

and MONTH(fs.live_from) = month(curdate() )

bazz

[r937]

it's not efficient, but at least it's correct, i.e. returns the correct results

[IBazz]

Just realised, I need to check the month_year against the fs.live_from.

So, how would I change my (poor), effort to do it properly and efficiently?

[r937]

is there a front-end language that could be used to generate the endpoints of the date range?

for efficiency, you want this format --

WHERE fs.live_from >= '2011-02-01'
  AND fs.live_from  < '2011-03-01'

by using the first of the following month as the upper end, combined with a "less than" comparison, you don't have to figure out whether you have feb 29th or not ...

you could still construct those dates in the query itself, using several date calculations based on CURRENT_MONTH, but it's easier if you do it in the front-end app and just pass in the two dates

[IBazz]

Thanks rudy,

I fumbled about - bit foggy at the mo - and went with 1st and 31st of the month. whatever date (28th or 29th etc) will always be <= 31st.

Is there much difference in efficiency terms, between using BETWEEN and using <= and >= ?

The docs are showing 'Service Temporarily Unavailable', which is why I ask here instead of looking it up.

bazz

[r937]

... will always be <= 31st.

but only if you're running in ALLOW_INVALID_DATES mode (which you shouldn't), otherwise it won't even let you say '2011-02-31'

also, if you're testing a datetime column (as opposed to a date column), then you will miss all the times of the day after midnight on the 31st

there is no performance difference between BETWEEN and the >= and <= (or <) conditions, it's just that correct results are a lot easier to obtain with the simple approach i suggested

[IBazz]

OK, rudy thanks again.

I'll build the exact end date in perl, though its fiddly when getting 1st jan, during december.

bazz

[r937]

... though its fiddly

can't be any worse than the sql equivalent...

WHERE fs.live_from >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
  AND fs.live_from  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                                   + INTERVAL 1 MONTH