The best way to check the current date is where sql query suggestion

Question

I am trying to find the most efficient way (best performance) for checking the date field for the current date. We currently use:

SELECT COUNT(Job) AS Jobs FROM dbo.Job WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)) 

Answer 1

 WHERE DateDiff(d, Received, GETDATE()) = 0 

Edit: As lined up in the comments for this answer, this is not an ideal solution. Check out the other answers in this thread.

Answer 2

If you just want to find all the records in which there is a date of receipt, and there are records with future dates received, then what you are doing (very very little) is wrong ... Since the "Between" operator allows values ​​that are equal to the final border, therefore you can get entries with Received date = until midnight tomorrow ...

If there is no need to use the index in Accepted, then all you need to do is check that the date diff with the current datetime is 0 ...

 Where DateDiff(day, received, getdate()) = 0 

This predicate, of course, is not SARGable, so it cannot use the index ... If this is a problem for this query, then assuming that you cannot get the receipt dates in the future, I would use it instead ...

 Where Received >= DateAdd(day, DateDiff(Day, 0, getDate()), 0) 

If the dates you receive may be in the future, then you are probably as close to the most effective as you can be ... (Except for the change between the characters "From" to ">" and "<)

Answer 3

If you need performance, you want to get a direct hit on the index without any CPU, etc. in line; as such, I would first compute the range and then use a simple WHERE query. I do not know what you are using, but the following works in SQL Server:

 // ... 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) 

Answer 4

which is pretty much the best way to do this. you can put DATEADD (d, DATEDIFF (d, 0, GETDATE ()), 0) and DATEADD (d, DATEDIFF (d, 0, GETDATE ()), 1) in variables and use them instead, but I don’t think that it will improve performance.

Answer 5

I'm not sure how you define “best,” but that will work fine.

However, if this request is what you intend to run again, you should get rid of the get_date () function and just insert the literal date value there using any programming language in which you use it. Despite their output, they change only once every 24 hours, get_date (), current_date (), etc. are non-deterministic functions, which means that your RDMS will probably invalidate the request as a candidate to be stored in the request cache if it has one.

Answer 6

Like a "fight"

  WHERE DATEDIFF(d, Received, GETDATE()) = 0 

Answer 7

I would usually use the solution proposed by Tomalak , but if you really desperately need performance, the best option would be to add an additional indexed ReceivedDataPartOnly field - to store data without a temporary part, and then 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