Results 1 to 4 of 4

Thread: This year only

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: This year only

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    Last edited by r937; 04-17-09 at 23:41.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124

    Current year only.

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj
    Doesn't this return records for the next year rather than only record from this year.
    no, i don't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •