CODEWITHSUNDEEP
mysql
Saeesh Tendulkar
Saeesh Tendulkar

Reputation: 703

How to get the week of the month along with the date range in MYSQL

I have the query which returns me the week of the month but I wanted the date range along with that. How do I alter my query to get the date range for the week.This is my query:

select 
 date,
 FLOOR((DAYOFMONTH(date) - 1) / 7) + 1 AS week,
 Concat('Week ',FLOOR((DAYOFMONTH(date) - 1) / 7) + 1) AS weekname 
from table_test 
 where DATE_FORMAT(date,'%m/%Y')='".$monYear."'" 
group by week

Here the variable $monYear equals "10/2017".

The week starts from sunday to saturday and the first week of the month starts from 01 So, the output should be like this:

Week 1 : 01/10/2107 - 07/10/2017

Week 2: 08/10/2017 - 14/10/2017

Week 3: 15/10/2017 - 21/10/2017

Week 4: 22/10/2017 - 28/10/2017

Week 5: 29/10/2017 - 31/10/2017

Ok so I tried this, its almost correct but it gives me wrong results for '09/2017'. It gives me 20170896 as startDay for week 1 (96 is wrong) and also it doesnt give me the slashes in between.

select date-dayofweek(date)+1 as startDay, 
date+ 7 - dayofweek(date) as endDate,
FLOOR((DAYOFMONTH(date) - 1) / 7) + 1 AS week,
from table_test where DATE_FORMAT(date,'%m/%Y')='09/2017' group by week

Upvotes: 0

Views: 3069

Answers (2)

Chris J
Chris J

Reputation: 1447

I'm sure someone can offer something maybe a little more 'slick', but this works in my tests:

SELECT 
date,
week,
weekname,
DATE_ADD(firstOfMonth,INTERVAL (week-1) WEEK) as 'Week Start',
IF(DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY) > eom, 
      eom,
      DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY)) as 'Week End'
FROM (
   SELECT
    date,
    FLOOR((DAYOFMONTH(date) - 1) / 7 +1) AS week,
    CONCAT('Week ',FLOOR((DAYOFMONTH(date) - 1) / 7) +1) AS weekname,
    DATE_ADD(date,interval -DAY(date)+1 DAY) AS firstOfMonth,
    LAST_DAY(date) as 'eom'
FROM table_test 
 WHERE DATE_FORMAT(date,'%m/%Y')='06/2017' 
GROUP BY week
) a

(Note: You have 31 Days in June in your example, when there's only 30, but the above otherwise matched your expected output for me)

+-----------+------+----------+------------+------------+
|   date    | week | weekname | Week Start |  Week End  |
+-----------+------+----------+------------+------------+
| 2017-06-06|  1   |  Week 1  | 2017-06-01 | 2017-06-07 |
| 2017-06-12|  2   |  Week 2  | 2017-06-08 | 2017-06-14 |
| 2017-06-17|  3   |  Week 3  | 2017-06-15 | 2017-06-21 |
| 2017-06-22|  4   |  Week 4  | 2017-06-22 | 2017-06-28 |
| 2017-06-29|  5   |  Week 5  | 2017-06-29 | 2017-06-30 |
+-----------+------+----------+------------+------------+

Upvotes: 3

Shadow
Shadow

Reputation: 34232

The dayofweek() function returns the position of the day within a week, with Sunday as 1.

yourdate-dayofweek(yourdate)+1 will give you the date of the first day of the week (assuming your first day of the week is Sunday).

yourdate + 7 - dayofweek(yourdate) will return the date of the last day of the week (assuming your first day of the week is Sunday).

If you want to have Monday as the first day, then use the weekday() function that returns the index of the day with Monday as 0.

yourdate-weekday(yourdate) will give you the date of the first day of the week in this case.

yourdate + 6 - weekday(yourdate) will return the date of the last day of the week.

Upvotes: 0

Related Questions