sql - Function for week of the month in mysql

Question

I was looking for a simple function to get the week of the month (rather than the easy week of the year) in a mysql query.

The best I could come up with was:

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

I'd love to know if I'm reinventing the wheel here, and if there is an easier and cleaner solution?

Answer 1

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.