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

Question

The year is divided into 12 months. We can break up a month in four weeks.

In MySQL, how to return the week of the month? (Example: first week: 2 entries, second week: 5 entries, third week: 3 entries, fourth week: 8 entries)

Using Week and WeekOfYear does not give the desired result, because functions return the week number of YEAR, not the month.

Answer 1

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

I would like to make it clear that this is probably not a very good way to share your data - (see comments), but it will allow you to get as close as possible.

Answer 2

Use the MySQL built-in week function to find the week number of the year, and then subtract the week number on the first day of the month.

 SELECT WEEK('2012-02-20') - WEEK('2012-02-01') 

there are many options in the documentation to customize the call according to what you are looking for, but I hope my example gives you an idea of ​​what is available to you.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

Answer 3

Week of the month (where the initial incomplete week is considered as a whole)

 select ceiling((day(now()) - (6 - weekday(date_format(now(),'%Y-%m-01'))))/7) + case when 6 - weekday(date_format(now(),'%Y-%m-01'))> 0 then 1 else 0 end week_of_month; 

Answer 4

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

Or you can see it here .