Business Week Group in SQL (instead of calendar week)

Question

My requirement is that I want to find business-week-ending (not the calender week) given a DATE column from the sales table in MSSQL.

Using different techniques I was able to find the [Calender] week-endings (and week-starting) dates corresponding to DATE in the table.

Since our business week ends on Wednesday [DOW 3 or 4 depending when the week started], I tried to deduct number of days from the week ending dates to pull it back to Wednesday. The idea did work pretty good with a flaw. Works fine as long as the Date in the table is greater than DOW 3 or 4. Any suggestion?

SELECT DateAdd(wk, DateDiff(wk, 0, Recons_Sales_Details.Recons_Date), 0) + 2

Answer 1

You need to look into SET DATEFIRST to do this:

SET DATEFIRST 4  --4 is Thursday week start

SQL Fiddle Demo