May 16 2016

MySQL week of the month

Tagged Under : ,

Normally if want display week of certain of the month, we can using WEEK() function. But WEEK() function only return you week of the year.

But now, we want display WEEK of the month no week of the year. that means first, second, third, fourth and fifth week of the month.

Below example is the SQL to display the week number of the month:
SELECT (WEEK(created_date,5) - WEEK(DATE_SUB(created_date, INTERVAL DAYOFMONTH(created_date)-1 DAY),5)+1) as week
The created_date column must be DATE or DATETIME type.