In Transact-SQL, the SET DATEFIRST command is used to set the first day of the week, so when you use the DATEPART(weekday, <Date>) function, you have control on which day of the week is first. So a common requirment is to set Monday as the first day of the week, so you'll call (Where 1 = Monday):
SET DATEFIRST 1;
1 = Monday, 2 = Tuesday, 3 = Wednesday, and so forth.
Very useful, but you cannot use SET commands within a View definition, so what do you do when you want to have a certain DATEFIRST value within a view's results? Use this calculation:
SELECT ((DATEPART(weekday, <Your Date>) + @@DATEFIRST -1 -<Your DATEFIRST>) % 7) + 1
<Your Date> with the date value
<Your DATEFIRST> with your diesired DATEFIRST number
And you can wrap this in your own Scalar-valued function to simplify reuse:
CREATE FUNCTION dbo.f_WEEKDAY(@dateValue datetime, @datefirst int)
RETURN ((DATEPART(weekday, @dateValue) + @@DATEFIRST -1 -@datefirst) % 7) + 1;
I hope this comes in handy for you, it has for me.
Business Intelligence Lead.