Is it possible to set mySQL to have a saturday as the start of the week?

Question

Is it possible to set mySQL to have a saturday as the start of the week?

I'm trying to run a query like:

SELECT DISTINCT(week(`date`)) 
  FROM `table` 
 WHERE `date` BETWEEN '2010-08-14' AND '2010-08-27'

But the week starts on a Saturday, not Sunday or Monday. (It's for a pay period week, not a regular week) It seems that the modes for the week function only offer Sunday or Monday as a starting date.

I've read that setting default_week_format can help but I don't think that will work for me as this is for specific reports only, not the entire system.

Do I have any other options?

Answer 1

It is possible to have Monday or Sunday, AFAIK there is no other option:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Answer 2

Cribbed from the comments from the link in Tomasz's answer:

Problem: To find week start and end date with user specified start of the week day and user specified date for which the week is to be found.

date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) day) week_start

date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) - 6 day) week_end