How to find the date range of a week

I am retrieving a list of records grouped by year and then by week number like this:

SELECT Id, YEAR(enquiry_date) AS the_year, DATE_FORMAT(enquiry_date, '%U') AS the_week_number FROM users GROUP BY the_year, the_week_number ORDER BY the_year ASC, the_week_number ASC

Code (markup):

I use this to populate a listbox that allows users to query data by the week number. It shows up in the listbox like "Week # 12 of 2006". However, instead of the week number I want to show the date range for that week. Can mysql do this?

 

Something like this. I left your functions in there as well. You should be able to remove them.

SELECT 
Id, 
YEAR(enquiry_date) AS the_year, 
DATE_FORMAT(enquiry_date, '%U') AS the_week_number 
CONCAT(DATE_ADD(enquiry_date, INTERVAL(1-DAYOFWEEK(enquiry_date)) DAY),'-',DATE_ADD(enquiry_date, INTERVAL(7-DAYOFWEEK(enquiry_date)) DAY)) AS DATE_RANGE
FROM users 
GROUP BY the_year, the_week_number 
ORDER BY the_year ASC, the_week_number ASC

Code (markup):

You may want to format differently. You can also select the range start and range end by not using CONCAT and selecting each seperately

 

It works, thanks!