Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the last day of the Week a Friday, Saturday, or Sunday ??? In your example, those are Saturdays. –  RolandoMySQLDBA Mar 28 '12 at 18:44
    
Another Question : If the Last Day of the Month is 2012-01-31 and the End of Week is Saturday, then would that count as 2012-02-04 ??? –  RolandoMySQLDBA Mar 28 '12 at 18:59
    
Added to question: Last day of the week in my case is Sunday. –  randy melder Mar 28 '12 at 20:35

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
The WEEKDAY solution was exactly what I needed, thanks! –  randy melder Mar 28 '12 at 21:25

Use LAST_DAY().

Documentation is here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.