mysql - Week of the year for weeks starting with Saturday

Question

We have customers that currently have defined weeks starting either on Sat, Sun or Monday. Came across these DATE_FORMAT options which nicely handle the week starting on Sunday and Monday but can't find a way to do same for week starting on Saturday. Any suggestions?

%U    Week (00..53), where Sunday is the first day of the week
%u    Week (00..53), where Monday is the first day of the week

Answer 1

I had a similar issue: I needed to calculate week numbers based on the following rules:

• Week starts on Friday
• The remainder days of an year (all the days after the last Friday of the year that do not complete a week) should be counted in the first week of the next year.

For example:

• 27/12/2012 (Thursday) should be Week 52 of 2012
• 28/12/2012 (Friday) should be Week 1 of 2013
• Week 1 2013 goes from 28/12/2012 to 3/1/2013

I made this statement that calculates both the YEAR and WEEKNUMBER based on these rules that you can easily adapt to your circunstance:

SELECT IF(ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7) > 52, YEAR(current_date)+1, YEAR(current_date)), 
        IF(ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7) > 52, 1, ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7));

The tricky part is just this expression:

ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7)

The rest (If clauses) are just for adapting the result of the expression to make year+1 and week = 1 on week 53.

I'll try to explain the expression as best as I can. The following expression gives you the week number pure simple (the day of the year divided by 7 days in a week rounded up):

ceil(( dayofyear(current_date))/7)

But now you want to make it start on Friday (or any other day). To do this you need to add to the current day, the days of the first week that were part of the previous year (it's like your current actually started a few days before, because your first week contains days from the previous year). This expression calculates that offset based on the weekday on Jan/1:

dayofweek(date_format(current_date, '%Y-01-01'))+OFFSET

The offset is the difference between 7 and the weekdaynumber you want the week to start:

• 0 for Saturday
• 1 for Friday
• 2 for Thursday
• 3 for Wednesday ...

So now you just have to add it to the previous one resulting in the above mentioned expression that calculates the week numbers starting on any weekday and assuming week 1 to start on the previous year:

ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+OFFSET )/7)

Then I just added an IF that turns week 53 into week 1 and another to add 1 to the year if it's week 53.