Take the 2-minute tour ×

I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using:

FROM         dbo.Job
                        AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1))
share|improve this question
Please note that the answer you selected is NOT the best performance way. Your way is close to the correct way, you just need to switch to >= and < rather than using BETWEEN (which is like <= on the second condition and is incorrect). –  ErikE Sep 13 '10 at 0:12

7 Answers 7

up vote 4 down vote accepted
  DateDiff(d, Received, GETDATE()) = 0
share|improve this answer
I wouldn't do it that way, as it is not SARG-able. –  Mitch Wheat Nov 28 '08 at 15:04
@Mitch Wheat: As long as there is no column with the date part only AND an index on it, nothing you can do will be SARGable anyway. –  Tomalak Nov 28 '08 at 15:13
Tomalak, No - his initial solution is SARGable... Where Received >= {Midnight This morning} And Received < {Midnight Tonight} is SARGable –  Charles Bretana Nov 28 '08 at 15:45
What the heck is "SARG-able"? –  James Curran Dec 1 '08 at 12:27
That means that a search condition can be satisfied by using an index (en.wikipedia.org/wiki/Sargable). Calculating the value that is being filtered on makes an operation non-SARGable, because calculated values are in no index. This forces the server to look at each row individually (=slow). –  Tomalak Dec 1 '08 at 13:48

If you just want to find all the records where the Received Date is today, and there are records with future Received dates, then what you're doing is (very very slightly) wrong... Because the Between operatior allows values that are equal to the ending boundary, so you could get records with Received date = to midnight tomorrow...

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

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

This predicate is of course not SARGable so it cannot use an index... If this is an issue for this query, then, (assuming you cannot have Received dates in the future??), I would use this instead...

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

If Received dates can be in the future, then you are probably as close to the most efficient as you can be... (Except change the Between to a >= AND < )

share|improve this answer
Charles, even without the index, DateDiff(day, received, getdate()) is not best because it forces a calculation on every row in the table, using more CPU for no reason. –  ErikE Sep 13 '10 at 0:15
@Emtucifor, true, but compared with Disk I/O reads, cpu cycles are so insignificant as to be irelevant. We're talking about three to four orders of magnitude different here. –  Charles Bretana Sep 13 '10 at 2:42
That's true, Charles. Thanks for putting my nitpicking in perspective. :) I do think it's best to recommend the latter where possible because when there IS an index, that will seriously affect I/O. –  ErikE Sep 13 '10 at 4:08
@Emtucifor, you are correct again.. and of course, best of all, when you have the discretion to do so, is to put the appropriate index in place, and design the queries to use them. –  Charles Bretana Sep 13 '10 at 12:08

If you want performance, you want a direct hit on the index, without any CPU etc per row; as such, I would calculate the range first, and then use a simple WHERE query. I don't know what db you are using, but in SQL Server, the 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)

FROM         dbo.Job
WHERE     (Received >= @DayStart AND Received < @DayEnd)
share|improve this answer
I am not sure what you mean by a "direct" hit on the index ? if you are simply talking about when there is a calculation on the "other" side of a predicate operator, instead of pre-calculating it before executing the index, then either A) the calculated value is based on some other column in the table and is not the same for generated row, so it has to be in the sql, or B) if it is the same value for every generated row, the query processor will pre-calculate it anyway, so it WILL only be calculated once, no matter how many rows the query produces. –  Charles Bretana Sep 13 '10 at 12:12

that's pretty much the best way to do it. you could put the DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) and DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1) into variables and use those instead but i don't think that this will improve performance.

share|improve this answer

I'm not sure how you're defining "best" but that will work fine.

However, if this query is something you're going to run repeatedly you should get rid of the get_date() function and just stick a literal date value in there via whatever programming language you're running this in. Despite their output changing only once every 24 hours, get_date(), current_date(), etc. are non-deterministic functions, which means that your RDMS will probably invalidate the query as a candidate for storing in its query cache if it has one.

share|improve this answer

How 'bout

      DATEDIFF(d, Received, GETDATE()) = 0
share|improve this answer
This is not the best way. See Marc Gravell's post for the best way. –  ErikE Sep 13 '10 at 0:13

I would normally use the solution suggested by Tomalak, but if you are really desperate for performance the best option could be to add an extra indexed field ReceivedDataPartOnly - which would store data without the time part and then use the query

declare @today as datetime
set @today = datediff(d, 0, getdate())

    count(job) as jobs
    received_DatePartOnly = @today
share|improve this answer
The solution suggested by Tomalak is far from the best way. –  ErikE Sep 13 '10 at 0:13

Your Answer


