Function for Week of the Month in MySQL

In MySQL, how to return the week of the month?

FLOOR((DayOfMonth(dateCol)-1)/7)+1

I'd like to make it clear that this is probably not a good way to divide your data - (see the comments), but that this will get you as close as you can get.

Get the week of the month in MYSQL

You can play with the WEEK() function, and see if it suits your needs. Here I'm using WEEK(date, 3) that will return the week of the year from 1 to 53, starting from Mondays:

set @my_date = '2015-09-13';

SELECT
  WEEK(@my_date, 3) -
  WEEK(@my_date - INTERVAL DAY(@my_date)-1 DAY, 3) + 1
  AS week_number;

• WEEK(date, 3) will return the week of the year of the selected date
• WEEK(date - INTERVAL DAY(@my_date)-1 DAY, 3) will return the week of the year of the first day of the month of the selected date

It will return 1 for 01-March-2015 (because it's the first day of the month so it's week 1) and 2 for 02-March-2015 (because weeks starts from Mondays, so it's a new week). If this is not the desidered behaviour you should specify your requirements more precisely.

Please see a fiddle here.

Function for week of the month in mysql

AFAIK, there is no standard on the first week of month.

First week of year is the week containing Jan 4th.

How do you define first week of month?

UPDATE:

You'll need to rewrite your query like this:

SELECT  WEEK(dateField, 5) -
        WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField) - 1 DAY), 5) + 1

so that the year transitions are handled correctly, and the weeks start on Monday.

Otherwise, your query is fine.

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

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 |
+-----------+------+----------+------------+------------+

Get Date with Day of Week, Week Number, Month and Year

I asked the same question in portuguese Stake Overflow, and they found a simple solution.

Using str_to_date, year, week number and day of week.

%Y  Year, numeric, four digits
%U  Week (00..53), where Sunday is the first day of the week
%W  Weekday name (Sunday..Saturday)

SELECT  str_to_date('201437 Thursday', '%Y%U %W');

Result:

2014-09-18 00:00:00

Portuguese Stack Overflow Answer Link : https://pt.stackoverflow.com/questions/33046/obter-data-com-dia-da-semana-n%C3%BAmero-da-semana-m%C3%AAs-e-ano/33063#33063

Thanks to everyone who helped me

Determine Week number from a date(Month) using PHP

All you need is

ceil(date('d')/7);

So your function will look like

function getWeekday($date){
  return ceil(date('d',strtotime($date))/7);
}

Demo

Even though requirement is a little strange. Week of the month is not a well defined thing but according to your comments on various answers all you need is to see week 1 if the date is within 1st 7 days of the month. 2 for next 7, 3 for next 7, 4 for next 7 and 5 for the leftovers.

Output

D   W
1   1
2   1
3   1
4   1
5   1
6   1
7   1
8   2
9   2
10  2
11  2
12  2
13  2
14  2
15  3
16  3
17  3
18  3
19  3
20  3
21  3
22  4
23  4
24  4
25  4
26  4
27  4
28  4
29  5
30  5
31  5

---

Old answer

Simple! you need a capital W, not a lowercase one.

W

ISO-8601 week number of year, weeks starting on Monday (added in PHP 4.1.0)

return date('W',strtotime($date));

The lowercase one - which you are using - is

Numeric representation of the day of the week

Manual

---

Related Topics