Mysql date less than week operation

Suppose you ha开发者_开发百科ve two datetime values, date_a and date_b

What expression evaluates to true if date_a occurs within or less than the calendar week of date_b?

Just use MySQL's week() function.

Example:

mysql> create table daten (a date, b date);
mysql> insert into daten values ('2011-04-14', '2011-04-12');
mysql> insert into daten values ('2011-04-14', '2011-04-22');

mysql> select * from daten where (week(a)=week(b) and year(a)=year(b)) or a<b;
+------------+------------+
| a          | b          |
+------------+------------+
| 2011-04-14 | 2011-04-12 |
+------------+------------+

This will give you all records with a in the same or an earlier calendar week than b. Note that week(a)<=week(b) would not work, because weeks start again each year.

Note: There is more than one convention for counting calendar weeks (week starts on Sunday or on Monday, different start of first week of the year). You can pass an optional second parameter "mode" to week() to tell it which convention to use; see the docs. Of course for this problem only the start day of the week matters, not what the first week of the year is.

A more brute force method:

Select ...
From DateValues
Where date_a >= Date_Add( date_b, Interval -DayOfWeek( date_b ) + 1 Day )
    And date_a <= Date_Add( date_b, Interval -DayOfWeek( date_b ) + 7 Day )