What you are missing is the following from SQL Server Books Online under the SET DATEFIRST topic:
ValueFirst day of the week is
1Monday
2Tuesday
3Wednesday
4Thursday
5Friday
6Saturday
7Sunday
When you have @@DATEFIRST = 1, the first day of the week is Monday, not Sunday.
Also, DATEFIRST is a session setting, so it has no impact on other users or sessions on the same server. The initial value of DATEFIRST is the default value for the login's default language, so for US english it is 7.
I have a link to my own function for this, F_START_OF_WEEK, below. Note that the setting of DATEFIRST makes no difference to F_START_OF_WEEK; you just pass the week start date to the function.
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
A companion function F_END_OF_WEEK, posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links: