SQL: Where between two dates without year?

Question

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.

Answer 1

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].

Answer 2

Supposed you have a date like this,

create table datelist
(
  d date
);

insert into datelist values
('2012-07-01'),
('2011-06-29'),
('2012-07-02'),
('2010-07-05'),
('2012-05-31'),
('2010-06-30');

Try this query below,

SELECT d, date_format(d,'%Y-%b-%d')
FROM datelist
WHERE (MONTH(d) = 6 AND DAYOFMONTH(d) >= 30)
   OR (MONTH(d) = 7 AND DAYOFMONTH(d) <= 27)

SQLFiddle Demo

Answer 3

Is it OK if the solution is terribly slow?

    SELECT tbl.*
      FROM tbl
INNER JOIN (SELECT COALESCE(DATE(CONCAT(yyyy, '-', MONTH(CURRENT_DATE), '-', DAYOFMONTH(CURRENT_DATE)),
                            DATE(CONCAT(yyyy, '-02-28'))) AS midpoint
              FROM (SELECT DISTINCT(YEAR(d)) AS yyyy
                     FROM tbl) all_years) adjusted
           ON tbl.datecol BETWEEN adjusted.midpoint - INTERVAL 2 WEEK
                                  AND
                                  adjusted.midpoint + INTERVAL 2 WEEK;

That computes all midpoints for all years in the data set, and then pulls records +- 2 weeks from any such midpoint, which handles end-of-year wrapping.

The COALESCE handles 02-29 on years without leapday (which MySQL will NULL-ify), forcing it down to 02-28.