Alex Rivera | Logout

SQL: Where between two dates without year?

Asked 2012-09-14T01:39:27.240
9

I'm trying to query through historical data and I need to return data just from a 1 month period: 2 weeks back and 2 weeks forward,but I need the year to not matter.

So, if I was to make the query today I would want all rows with date between xxxx-06-31 and xxxx-07-27

Thanks in advance for the help!

EDIT: I've tried two ways. both of which I believe will not work around the new year. One is to use datepart(day) and the other would be to simply take the year off of date and compare.

mysql sql
Edit
Report

1 Answer

9

The best way to think of this problem is to convert your dates to a number between 0 and 365 corresponding to the day in the year. Then simply choosing dates where this difference is less than 14 gives you your two week window.

That will break down at the beginning or end of the year. But simple modular arithmetic gives you the answer.

Fortunately, MySQL has DAYOFYEAR(date), so it's not so complicated:

SELECT * FROM tbl t
WHERE 
  MOD(DAYOFYEAR(currdate) - DAYOFYEAR(t.the_date) + 365, 365) <= 14
  OR MOD(DAYOFYEAR(t.the_date) - DAYOFYEAR(currdate) + 365, 365) <= 14

That extra + 365 is needed since MySQL's MOD will return negative numbers.

This answer doesn't account for leap years correctly. If the current year is not a leap year and the currdate is within 14 days of the end of the year, then you'll miss one day in Jan that you should have included. If you care about that, then you should replace 365 with [the number of days in the year - 1].

answered 2012-09-14T03:43:56.023

Your Answer