How to Get List of weeks between the given date range?

Question

I want week list with week number, week start date and end date between two dates. Let me give you an example,

If I am passing start date as 2019-12-11 and end date as 2019-12-25, then expected output should look like below:

Week Number | Week start date | Week end date

W1           2019-12-11       2019-12-14     
W2           2019-12-15       2019-12-21     
W3           2019-12-22       2019-12-25

I have tried using below query but I got output like

 Week start date | Week end date

 2019-12-15        2019-12-21     
 2019-12-22        2019-12-28

select start_date, date_add(start_date, INTERVAL 6 DAY) as end_date
from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) start_date
from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where start_date between '2019-12-11' and '2019-12-25'
AND date_format(start_date, '%w') = 0

An RDMS is for the storage and retrieval of data. I don't see how this relates to that. — Strawberry, Dec 13 '19 at 07:56

Sebastian Brosch · Accepted Answer · 2019-12-13T08:58:04.210

You can use the following solution using WEEK function and ROW_NUMBER:

-- calendar table: https://stackoverflow.com/a/45992247/3840840
SELECT CONCAT('W', ROW_NUMBER() OVER (ORDER BY WEEK(start_date))) AS `Week Number`, 
  MIN(start_date) AS `Week start date`, MAX(start_date) AS `Week end date`
FROM (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS start_date
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) v
WHERE start_date BETWEEN '2019-12-11' AND '2019-12-25'
GROUP BY WEEK(start_date)
ORDER BY WEEK(start_date)

By using WEEK you don't have to calculate the weeks yourself. MySQL can detect the weeks. With WEEK you can get the week number of a date value. You can group by this week number to get the rows for every week. With MIN and MAX on the date value you get the first and last date of each week.

With ROW_NUMBER you can add a row number to your result. This function is available since MySQL 8.0. On earlier versions of MySQL you can use a solution like the following:

SELECT CONCAT('W', @row_number:=@row_number+1) AS `Week Number`, 
  MIN(start_date) AS `Week start date`, MAX(start_date) AS `Week end date`
FROM (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS start_date
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
  ORDER BY start_date
) v, (SELECT @row_number:=0) rn
WHERE start_date BETWEEN '2019-12-11' AND '2019-12-25'
GROUP BY WEEK(start_date)
ORDER BY WEEK(start_date)

demo on dbfiddle.uk

Can you please add some explanation of how this may solve OPs question, along with any assumptions (i.e. database version) — Nigel Ren, Dec 13 '19 at 07:52

How to Get List of weeks between the given date range?

1 Answers1