ArkWare (4/7/2010)
mbarkell (4/7/2010)
On my system using the query for the first day of the week gives Monday instead of Sunday which is, of course, incorrect. Is this based on the locale of the system, or is that consistent behavior all together. After all, the first day of the week is always Sunday not Monday.mbarkell,
check @@DATEFIRST (Transact-SQL) in BOL...
Arkware
Great Question.
This is a tricky one. DATEDIFF doesn't actually honor datefirst or locale settings. Here's an article (also by Itzik Ben-Gan) that talks about it.
The reason you're getting monday is because 1/1/1900 was a monday. To adjust that particular query to get a sunday instead, you can use this:
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)
I should probably add that into the article.