How do I compare against the current week using SQL Server?

How do I compare an SQL Server date column against the current week开发者_Go百科?

For instance:

WHERE [Order].SubmittedDate = *THIS WEEK*

You could convert your date to a week number and compare this to the week number from the current date. Likewise, you'll need to compare the year as well, so that you don't get last year's weeks.

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

Assuming you are meaning always "this week" and there are no records with Submitted dates in the future, which I imagine could be the case you can do:

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

If dates do go into the future, the full restriction to 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)

I'd strongly recommend using a clause based on a start and end date like this, as it will allow efficient index use so should perform better.

Try this:

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

Maybe this can run faster, as doesn't needs to be evaluated everytime:

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