This year only

oldnickj · #1 (**permalink**) 04-17-09, 14:52

This filter returns every entry from now into the future, how do I limit to this year only (AND datestart_eve < (next year)

WHERE race_region.id_reg=3 AND datestart_eve > NOW()

Nick

r937 · #2 (**permalink**) 04-17-09, 22:37

for the lower bound, if today is january 1, you want everything that is greater than or equal to today, i.e. greater than today - 0 days

if today is january 2, you want everything that is greater than or equal to yesterday, i.e. greater than today - 1 days

thus the general pattern for the lower bound is today minus the number of days that we are into this year, plus 1 day, i.e. DAYOFYEAR() + 1

using that formula gives us january 1st of this year, so if we add 1 year plus, we have the upper bound

Code:

WHERE ... 
  AND datestart_eve >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE) + 1 DAY
  AND datestart_eve  < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE) + 1 DAY + INTERVAL 1 year

make sense?

note: "greater than or equal to" for the lower bound, but "less than" for the upper bound

this formula works whether the column in question (datestart_eve in this case) contains dates or datetime values

oldnickj · #3 (**permalink**) 04-18-09, 11:53

Doesn't this return records for the next year rather than only record from this year. I suppose I formed my question incorrectly. We ant the record for this year only. 2009 this year 2010 next year.

Nick