How To Get YEARWEEK() To See Sunday As The Start Of The Week?

Question

I'm trying to get the YEARWEEK function to see Sunday as the 1st day of the week.

An example date is: Sunday 1st Mar 2009

Here is my sql

SELECT YEARWEEK('2009-03-01')

and the result is

-> 200909

Which is week 9. I believe it is telling me this is week 9 because it sees the sunday as the last day of the previous week.

How would I get this function to see sunday as the 1st day of the week and therefore return as week 10?

Answer 1

this will do it:

SELECT YEARWEEK('2009-03-01')+IF(WEEKDAY('2009-03-01')=6,1,0);

EDIT: this is the better solution, for more information click here:

SELECT YEARWEEK('2009-03-01',0);

(but i don't know why you want to do this - 2009.03.01 is in week 9, not 10...)

Answer 2

According to http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_yearweek, YEARWEEK has an optional second argument which controls this. YEARWEEK('2009-03-01', 0) should do what you want (but see the table of possible values under WEEK on that page).