And worse off yet is if microsoft were to change the reference date to one that started on anything else other than a monday, you would have to modify your code to get it to work.
Nah. Just hard-code '1900-01-01' as the date instead of using 0 :-).
When I need a week to start on Sunday, I use Jan 01, 1950 as a base date, since it was a Sunday.
Hmm, not changing the DATEFIRST here - but I seem to get Sunday
That's because you used -1 as the base date, which would be 12/31/1899, which was a Sunday.
Scott Pletcher, SQL Server MVP 2008-2010