<------------- Okay, I earned one of these...
Michael Valentine Jones (11/3/2010)
What you are missing is the following from SQL Server Books Online under the SET DATEFIRST topic:When you have @@DATEFIRST = 1, the first day of the week is Monday, not Sunday.
:blush: That's what I get for not doublechecking someone's blog against the BOL. Thank you.
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'll be over here in the corner, trying NOT to look like the local idiot. And failing. Thank you for that. I knew that and pulled a moron because I couldn't remember if some of them were connection level or not (like the transaction level ones are)... which wouldn't matter in your own query analyzer window anyway.
(EDIT: Before I get called on transaction level not being connection setting in 2k8, it depends on version: http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx. Much of my knowledge comes from 2k.)
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.
Yeah, I was trying to test mine to make sure I didn't muck up a different datefirst. See how well that went? 😀
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:
Thanks for these, I'll check them out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA