How can I compare with the current week using SQL Server?

Question

How to compare SQL Server date column with current week?

For instance:

WHERE [Order].SubmittedDate = *THIS WEEK*

Answer 1

You can convert your date to a week number and compare it with the week number from the current date. Similarly, you will need to compare the year so that you do not get the last year of the week.

WHERE DATEPART(wk, [Order].SubmittedDate) = DATEPART(wk, GETDATE())
AND DATEPART(yy, [Order].SubmittedDate) = DATEPART(yy, GETDATE())

Answer 2

Assuming you always have the value “this week”, and in the future there are no entries with dispatch dates, which I suppose could be like this:

WHERE [Order].SubmittedDate >= DATEADD(dd, -(DATEPART(dw, GETDATE()) -1), GETDATE())

If dates come into the future, the full limit for this week is:

WHERE [Order].SubmittedDate >= DATEADD(dd, -(DATEPART(dw, GETDATE()) -1), GETDATE())
    AND [Order].SubmittedDate < CAST(CONVERT(VARCHAR(10), DATEADD(dd, (8 - DATEPART(dw, GETDATE())), GETDATE()), 120) AS DATETIME)

, , , , .

Answer 3

Try the following:

WHERE [Order].SubmittedDate BETWEEN
      DATEADD(d,   - DATEPART(dw, GETDATE()) + 1, GETDATE()) AND
      DATEADD(d, 7 - DATEPART(dw, GETDATE())    , GETDATE())

Perhaps this may work faster, since it does not need to be evaluated every time:

DECLARE @StartDate   DATETIME, 
        @EndDate     DATETIME 
SELECT  @StartDate = DATEADD(d,   - DATEPART(dw, GETDATE()) + 1, GETDATE()),
        @EndDate   = DATEADD(d, 8 - DATEPART(dw, GETDATE())    , GETDATE())

-- // Strip time part, so week starts on Sunday 00:00
SELECT  @StartDate = CAST(FLOOR(CAST(@StartDate AS FLOAT)) AS DATETIME),
        @EndDate   = CAST(FLOOR(CAST(@EndDate   AS FLOAT)) AS DATETIME)
...
WHERE [Order].SubmittedDate >= @StartDate AND [Order].SubmittedDate < @EndDate