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?
it's not efficient, but at least it's correct, i.e. returns the correct results
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?
is there a front-end language that could be used to generate the endpoints of the date range?
for efficiency, you want this format --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 ...Code:
WHERE fs.live_from >= '2011-02-01'
AND fs.live_from < '2011-03-01'
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
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.
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
OK, rudy thanks again.
I'll build the exact end date in perl, though its fiddly when getting 1st jan, during december. :(