And for the case of week starting on monday

SELECT
COUNT(*) AS reports_in_week,
DATE_ADD(date_created, INTERVAL(1-WEEKDAY(date_created)) DAY),
DATE_ADD(date_created, INTERVAL(7-WEEKDAY(date_created)) DAY)
FROM
nominations
GROUP BY
WEEK(date_created, 1)

Thanks for this, saved me loads of time in the long run.

For those of you trying to get the START of a week value beginning on Monday instead of on Sunday, I did find a slightly cleaner way in MySQL 5 to build this where the date is in a DB:

if(dayofweek(datevalue)=1,DATE(DATE_SUB(datevalue, Interval 6 DAY)), DATE(DATE_SUB(datevalue, INTERVAL(DAYOFWEEK(datevalue))-2 DAY)))

This will take the “datevalue” column from the DB, and modify it to force the previous Monday on the value.

Earlier code will have duplicate rows for the end of the year, for example

2013-12-30 – 2014-01-05
2013-12-30 – 2014-01-05

so to handle it put DISTINCT after SELECT, this query should do the trick:


SELECT DISTINCT
DATE_ADD(msci_date, INTERVAL(2-DAYOFWEEK(msci_date)) DAY),
DATE_ADD(msci_date, INTERVAL(8-DAYOFWEEK(msci_date)) DAY)
FROM
`master_schedule_csv_import`
GROUP BY
YEAR(msci_date),WEEK(`msci_date`)


Hi all, greetings.
Thank you Luke and others for the replies and discusson.
I made small tweaks reading discussions above so I share also here.
Thank you all, amazing and helpfull article.


SELECT
DATE_ADD(msci_date, INTERVAL(2-DAYOFWEEK(msci_date)) DAY) week_start,
DATE_ADD(msci_date, INTERVAL(8-DAYOFWEEK(msci_date)) DAY) week_end
FROM
master_schedule_csv_import
GROUP BY
YEAR(msci_date) + .01 * WEEK(msci_date)


@ olivier:
thank you. your query helped me solve my problem.

Thanks. Just the ticket.

Don’t call it a ‘major pain in the ass’. It’s creative SQL and good fun !

80)

Just wanted to add my two cents. I needed a query that would fetch me the date of Monday of a week of the specified date. I.e. if I have some date which is on Wednesday, I needed to get the date of the Monday on the same week.

The query is as follows:

SELECT DATE_SUB('2012-02-05', INTERVAL ((DAYOFWEEK('2012-02-05') + 12) % 7) DAY)

Obviously, you replace the date with your date field but this works :)

Can anybody help in writing the script(query).
I want to display the records in a way that page 1 contains the records of current week, page 2 contains the records of last week, page 3 contains the records of before last week, and so on….. Where Page # is the paging number…

Plz help in writing the query. I am using PHP/MySQL…

OH THANK YOU SO MUCH :D GODBLESS YOU you

Hi Rob,

Sorry for late reply.

Yes , but there is 1 more condition , week should starts from Monday if you check in the calendar 2011 u will find that ‘2011-04-01’ starts with Friday, so 1,2,3 april should be First week (should show value = 1) then afterwards should increase accordingly.

Thanks & Regards,
Avi