In one of my current projects, I wanted to create a view box that shows a list of regular meetings in the current week. The meeting table was:
Assuming that the current date is 22/10/2009, then the view box will show the records with ids (1 and 2). Where as the record with the id 3 is for a past meeting on Wednesday (21/10/2009).
SELECT * FROM meetings as m WHERE DAYOFWEEK(startdate) BETWEEN DAYOFWEEK(NOW()) AND '7' AND enddate > NOW() ORDER BY startdate ASC
This query will do what I want, but it has one drawback. DAYOFWEEK() function return 1 for Sunday, but in New Zealand the first day of the week is Monday.
My second attempt
SELECT * FROM meetings as m WHERE IF(DAYOFWEEK(startdate) = 1, 7, DAYOFWEEK(startdate)-1) BETWEEN IF(DAYOFWEEK(NOW()) = 1, 7, DAYOFWEEK(NOW())-1) AND '7' AND enddate > NOW() ORDER BY startdate ASC
This query is the same as the first one, the differences are on line 2 and 4.
On line 2, instead of directly getting the index value of the startdate, I have added an if statement to check if the current index is equal to 1 (Sunday), then change it to 7 (last day of the week). For all the other days in the week minus 1 from the index.
WHERE IF(DAYOFWEEK(startdate) = 1, 7, DAYOFWEEK(startdate)-1)
Line 4 is the same as line 2.
IF(DAYOFWEEK(NOW()) = 1, 7, DAYOFWEEK(NOW())-1)
This query will return the result I want 🙂
Your comments are welcome.