DATEPART and DATEFIRST

I am working on a query that groups data by the week-ending date.

In MS-Access, you can use DatePart to get the day of the week ( 1 to 7 ) and then add 6 thru 0 days to your test date to calculate the week-ending date. For a normal Sunday - Saturday workweek, if your date field is TheDate you would use this:

DateAdd("d", 7 - DatePart("w", TheDate), TheDate)

...and if your workweek if different, e.g. Saturday - Friday you can use the optional argument to specify the 1st day of the week:

DateAdd("d", 7 - DatePart("w", TheDate, vbSaturday), TheDate)

Now, if you are working in SQL Server, the basic Sunday - Saturday expression is similar:

DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)

However, in T-SQL there is no optional parameter for the 1st day of the week - you would need to do this:

SET DATEFIRST 6

SELECT
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
FROM TheTable

...but the DATEFIRST is a global setting, so it might affect other code that depends on the default setting.

One possible solution is this:

IF @@DATEFIRST != 7
SET DATEFIRST 7

SELECT
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
FROM TheTable

...which forces the 1st day to the default, and then uses 6 instead of 7 to calc the offset days. This will work in a stored procedure, but not in a view.

OTOH if you are certain that the DATEFIRST never varies from 7, you could simply use

DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)

which does give the correct result for the Saturday - Friday workweek & can be used in sp's and views.