[ACCEPTED]-Best way to check for current date in where clause of sql query-performance
WHERE DateDiff(d, Received, GETDATE()) = 0
Edit: As lined out in the comments to this 2 answer, that's not an ideal solution. Check 1 the other answers in this thread, too.
If you just want to find all the records 18 where the Received Date is today, and there 17 are records with future Received dates, then 16 what you're doing is (very very slightly) wrong... Because 15 the Between operator allows values that 14 are equal to the ending boundary, so you 13 could get records with Received date = to 12 midnight tomorrow...
If there is no need 11 to use an index on Received, then all you 10 need to do is check that the date diff with 9 the current datetime is 0...
Where DateDiff(day, received, getdate()) = 0
This predicate 8 is of course not SARGable so it cannot use 7 an index... If this is an issue for this 6 query then, assuming you cannot have Received 5 dates in the future, I would use this instead...
Where Received >= DateAdd(day, DateDiff(Day, 0, getDate()), 0)
If 4 Received dates can be in the future, then 3 you are probably as close to the most efficient 2 as you can be... (Except change the Between 1 to a >= AND < )
If you want performance, you want a direct hit 5 on the index, without any CPU etc per row; as 4 such, I would calculate the range first, and 3 then use a simple WHERE query. I don't know 2 what db you are using, but in SQL Server, the 1 following works:
// ... where @When is the date-and-time we have (perhaps from GETDATE()) DECLARE @DayStart datetime, @DayEnd datetime SET @DayStart = CAST(FLOOR(CAST(@When as float)) as datetime) -- get day only SET @DayEnd = DATEADD(d, 1, @DayStart) SELECT COUNT(Job) AS Jobs FROM dbo.Job WHERE (Received >= @DayStart AND Received < @DayEnd)
that's pretty much the best way to do it. you 4 could put the DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) and 3 DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1) into 2 variables and use those instead but i don't 1 think that this will improve performance.
I'm not sure how you're defining "best" but 11 that will work fine.
However, if this query 10 is something you're going to run repeatedly 9 you should get rid of the get_date() function 8 and just stick a literal date value in there 7 via whatever programming language you're 6 running this in. Despite their output changing 5 only once every 24 hours, get_date(), current_date(), etc. are 4 non-deterministic functions, which means that your RDMS will 3 probably invalidate the query as a candidate 2 for storing in its query cache if it has 1 one.
WHERE DATEDIFF(d, Received, GETDATE()) = 0
I would normally use the solution suggested by Tomalak, but if you are 5 really desperate for performance the best 4 option could be to add an extra indexed 3 field ReceivedDataPartOnly - which would 2 store data without the time part and then 1 use the query
declare @today as datetime set @today = datediff(d, 0, getdate()) select count(job) as jobs from dbo.job where received_DatePartOnly = @today
Compare two dates after converting into 1 same format like below.
where CONVERT(varchar, createddate, 1) = CONVERT(varchar, getdate(), 1);
More Related questions