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.
