SQL Query to get last day of every week

Question

Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day (day of month 01-28|29|30|31) for that week.

Output E.g.

    COUNT(*) | TheDate
    11       | 2012-01-07
    22       | 2012-01-14
    123      | 2012-01-21

Help?

Note: Last day of the week in my case is Sunday.

Answer 1

You need to use the DAYOFWEEK function

If the End of the Week in Saturday, any date can be morphed into a Saturday.

For example, this computes the upcoming Saturday

SELECT DATE(NOW() + INTERVAL (7 - DAYOFWEEK(NOW())) DAY);

This computes the upcoming Sunday (you must use WEEKDAY function instead)

SELECT DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY); 

For a table called mytable with a Date Column called theDate, your summation would be

Saturday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (7 - DAYOFWEEK(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Sunday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (6 - WEEKDAY(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Give it a Try !!!

CAVEAT if you want any other end of week other than Saturday and Sunday, I wrote an insane algorithm back in Sep 22, 2011 for computing any week starting and ending whatever day.

Answer 2

Use LAST_DAY().

Documentation is here.