Calculate Week of Year starting on a Monday
I am putting together a training log for WalkJogRun members to log their training data and ran into a spot with ColdFusion 8 showing Sunday as the first day of the week. Exercise training plans generally start on a Monday so I had to find a way around it.
The US starts it's week on a Sunday, the UK and a lot of other countries use Monday as the first day. For the most part Java is hiding that detail from you since java servers in the US will use Sunday as the first day of the week, whereas in the UK it will use Monday. I assume (rightly or wrongly) that the same is true for ColdFusion since it is built on Java.
To be explicit in ColdFusion you can construct a Java calendar object, set the first day of the week with setFirstDayOfWeek and then use a dateformat object to generate a week number starting on a Monday.
However, I realized after looking into these options my problem was the week() function of MySql. Arg. It turns out that date_format() for mysql has a format specifier %u for week of year beginning on a Monday or %U for week of year beginning on a Sunday. Perfect:
FROM training_data
GROUP BY date_format(training_date,"%u")
ORDER BY date_format(training_date,"%u") DESC