How do I compare an SQL Server date column against the current week?
For instance:
WHERE [Order].SubmittedDate = *THIS WEEK*
views:
180answers:
3
+3
A:
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())
nbolton
2010-01-14 11:45:15
Be aware you may notice a performance hit as this is a non-sargable condition so prevents best index use. see http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx
AdaTheDev
2010-01-14 11:54:07
+1 Nice tip. It may look simpler but it's not necessarily good performance.
nbolton
2010-01-14 11:54:49
do you have a way of making this perform faster?
Andy
2010-01-14 11:57:47
@Andy - yes, see my answer. Basically, don't wrap the field being queried in a function call. Instead, you work out the start and end date of the period you're interested in and query with that date range
AdaTheDev
2010-01-14 12:01:30
A:
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
Rubens Farias
2010-01-14 11:47:55
Technically you can miss records using this (slim chance) - what about records created in that last second (e.g. 23:59:59.500)? Hence I always prefer to use a >=.... AND <.... approach using 00:00:00 of the next day as the < end date
AdaTheDev
2010-01-14 12:07:05
It's because I've seen instances where this has actually produced incorrect results in the wild and is quite easy to overlook when trying to find an issue so was worth noting.
AdaTheDev
2010-01-14 12:11:55
+1
A:
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.
AdaTheDev
2010-01-14 11:51:54