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?
解决方案
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