Select dates within the Current Week using SQL Server

One of the most common date queries I have seen is to select the dates matching the current week. Here’s how to do it

SAMPLE DATA

DECLARE @TT TABLE (CheckOut DATETIME)
INSERT INTO @TT VALUES ('March 15, 2010 11:25pm')
INSERT INTO @TT VALUES ('April 11, 2010 9:55am')
INSERT INTO @TT VALUES ('April 07, 2010 11:24am')
INSERT INTO @TT VALUES ('April 05, 2010 11:22pm')
INSERT INTO @TT VALUES ('April 13, 2010 5:27am')
INSERT INTO @TT VALUES ('April 16, 2010 8:21pm')
INSERT INTO @TT VALUES ('April 12, 2010 11:55am')
INSERT INTO @TT VALUES ('April 02, 2010 10:16am')
INSERT INTO @TT VALUES ('April 06, 2010 11:35am')
INSERT INTO @TT VALUES ('April 18, 2010 9:49pm')
INSERT INTO @TT VALUES ('April 12, 2010 7:55pm')

QUERY

SELECT GETDATE() as CurrentDateTime

SELECT * FROM @TT
WHERE CheckOut >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND CheckOut < DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)

OUTPUT

As you can see, the query selects the dates only matching the current week.