Z Holt Z Holt - 5 months ago 33
MySQL Question

MySQL date between last monday and the next sunday

i'm looking to find all records that have a booking date between the previous monday and the next sunday in MySQL.

So far I have:

SELECT firstname
, lastname
, sessions
, (SELECT COUNT(memberid)
FROM bookings
WHERE m.memberid = b.memberid
and b.date between lastMonday and nextSunday) as sessionsused
from members


I'm looking what to substitute into the lastmonday and nextsunday

Any help is much appreciated!

Answer

MySQL's YEARWEEK() function selects a unique value for each week that you can use for comparison. It takes a second parameter which specifies whether weeks start on Sunday (0) or Monday (1).

SELECT COUNT(memberid) 
FROM bookings
WHERE m.memberid = b.memberid  
AND YEARWEEK(b.date, 1) = YEARWEEK(NOW(), 1);

This will always select rows where b.date is in the current week. For a specific week in the past, change NOW() for whatever date expression you require.

For the more generic case where your week does not start on a Sunday or a Monday, you will need some slightly more complicated logic. Here you substitute @weekday with the day on which your weeks begin, 2 = Tues, 3 = Wed, 4 = Thu, 5 = Fri, 6 = Sat.

SELECT COUNT(memberid) 
FROM bookings
WHERE m.memberid = b.memberid  
AND DATE(b.date)
    BETWEEN DATE_SUB(DATE(NOW()), INTERVAL (WEEKDAY(NOW()) - @weekday + 7) % 7 DAY)
        AND DATE_ADD(DATE(NOW()), INTERVAL 6 - (WEEKDAY(NOW()) - @weekday + 7) % 7 DAY);