paul.j.kemna (9/25/2013)
We don't own the calendar table, so that is not really an option.Anyway, my boss and I came up with this code as an option as well:
DECLARE @someDate as DATE = '2013-09-26'
select CASE WHEN DATEPART(DW, @someDate) >= 4 THEN DATEADD(d,4-DATEPART(DW, @someDate), @someDate) ELSE
DATEADD(d,4-DATEPART(DW, DATEADD(D, -7, @someDate)),DATEADD(D, -7, @someDate)) END
Weeks of the year is not necessary in this instance. We simply needed to be able to group by a week start of Wednesday, and display the past X weeks. (I chose 8 as a place to start).
PK
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.
Try this to return the datetime of the Wednesday on or before @someDate at time 00:00:00.000 (midnight). You can cast the result back to DATE if you prefer.
declare @someDate as DATE = '2013-09-26'
Select Wed = dateadd(dd,((datediff(dd,'17530103',@someDate)/7)*7),'17530103')
More info here:
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Edit: Modified code to use parameter of type DATE