CODEWITHSUNDEEP
sqlmysqlfunction
YiSh
YiSh

Reputation: 367

Function for week of the month in mysql

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?

Upvotes: 11

Views: 14236

Answers (5)

Nadia Noversega
Nadia Noversega

Reputation: 59

Might be 12 years too late but in case anyone still looking, I am using this calculation in bigquery MySQL for calculating week in month.

I'm using Monday as first day on my calculation

case when 
        (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7 
                          else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) > 1 then -- check first day of month to decide if it's a complete week (starts on Monday)
                case when EXTRACT(DAY FROM my_date) <= 7 then -- for incomplete week
                    case when 
                            (case when EXTRACT(DAYOFWEEK FROM my_date) = 1 then 7 else EXTRACT(DAYOFWEEK FROM my_date)-1 end)  -  EXTRACT(DAY FROM my_date) =
                                 (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7 
                                   else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -1 then 1 -- incomplete week 1
                      else FLOOR(( EXTRACT(DAY FROM my_date) + (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7
                                                                 else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -2 )/7)+1 end  -- calculate week based on date
            else FLOOR(( EXTRACT(DAY FROM my_date) + (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7 
                                                         else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -2 )/7)+1 end -- calculate week based on date
     else FLOOR((EXTRACT(DAY FROM my_date)-1)/7)+1 -- for complete week
        end

The idea is to add the day difference (Monday to whatever day 1st of that month is) to date so it would divide correctly for week > 1

For week 1 (< date 7), I am calculation using day of week - date to get the end of 1st incomplete week (1st not on Monday).

Upvotes: 1

Rafael
Rafael

Reputation: 23

My solution with a week starts on a Sunday.

SELECT    ( 1 + ((DATE_FORMAT( DATE_ADD(LAST_DAY( DATE_ADD('2014-07-17',
    INTERVAL -1 MONTH)), INTERVAL 1 DAY),'%w')+1) + 
    (DATE_FORMAT('2014-07-17', '%d')-2) ) DIV 7) "week_of_month";

Upvotes: 1

Solarflare
Solarflare

Reputation: 11096

(Just to clarify for future readers: this answer to this old question was added because of a bounty that implied the current answer wasn't satisfying, so I added this solution/definition with additional "configuration options" for all kinds of situations.)

There is no standard definition for the Week of month, but a general solution would be the following formula, that you can configurate to your needs:

select (dayofmonth(dateField) + 6 + (7 - "min_days_for_partial_week") 
        - (weekday(datefield) - "weekday_startofweek" + 7) MOD 7) DIV 7 as week_of_month;

where

  • "weekday_startofweek" has to be replaced by the weekday that you want to be the first day of the week (0 = Monday, 6 = Sunday).
  • "min_days_for_partial_week" is the number of days the first week has to have to count as week 1 (values 1 to 7). Common values will be 1 (the first day of the month is always week 1), 4 (this would be similar to "iso week of the year", where the first week of the year is the week that contains the thursday, so has at least 4 days), and 7 (week 1 is the first complete week).

This formula will return values 0 to 6. 0 means that the current week is a partial week that doesn't have enough days to count as week 1, and 6 can only happen when you allow partial weeks with less than 3 days to be week 1.

Examples:

If your first day of the week is Monday ("weekday_startofweek" = 0) and week 1 should always be a whole week ("min_days_for_partial_week" = 7), this will simplify to 

select (dayofmonth(dateField) + 6 - weekday(datefield)) DIV 7 as week_of_month;

E.g., for 2016-06-02 you will get 0, because June 2016 started with a Wednesday and for 2016-06-06 you will get 1, since this is the first Monday in June 2016.

To emulate your formula, where the first day of the week is always week 1 ("min_days_for_partial_week" = 1), and the week starts with Sunday ("weekday_startofweek" = 6), this will be

select (dayofmonth(dateField) + 12 - (weekday(datefield) + 1) MOD 7) DIV 7 as week_of_month;

Although you might want to comment that properly to know in 2 years where your constants came from.

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

There is an alternative that's sometimes used in reporting databases. It's to create a table, let's call it ALMANAC, that has one row per date (the key), and has every needed attribute of the date that might be useful for reporting purposes.

In addition to the week of the month column, there could be a column for whether or not the date is a company holiday, and things like that. If your company had a fiscal year that starts in July or some other month, you could include the fiscal year, fiscal month, fiscal week, etc. that each date belongs to.

Then you write one program to populate this table out of thin air, given a range of dates to populate. You include all the crazy calendar calculations just once in this program.

Then, when you need to know the attribute for a date in some other table, you just do a join, and use the column. Yes, it's one more join. And no, this table isn't normalized. But it's still good design for certain very specific needs.

Upvotes: 8

Quassnoi
Quassnoi

Reputation: 425271

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.

Upvotes: 17

Related Questions