Date manipulation question

Databases
1

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

2

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

3

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?

4

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

5

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

6

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

7

OK, rudy thanks again.

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

bazz

8

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

:slight_smile: