I came across a little gem the other week and use it all the time when I need to measure results over a period of weeks - this identifies the start of the current week so can be easily used in a variable, for example, as an 'end date' parameter in my query. It goes like this:-
SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
I often use it in a variable in the following way:-
DECLARE @start as DATE DECLARE @end as DATE SET @start = '04/01/2016' -- The start of the current year using the ISOWEEK numbering scheme SET @end = SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) SELECT * FROM Database WHERE CreatedDate >= @start and CreatedDate < @end
And there you go - hopefully helpful to you.