Best way to check for current date in where clause of sql query

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

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

Answers


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

Edit: As lined out in the comments to this answer, that's not an ideal solution. Check the other answers in this thread, too.


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


Need Your Help

Xcode : Is there a way to search string in expressions defined in breakpoints?

ios xcode lldb

I followed this tuto about debugging in XCode. I got rid of some NSLog instructions in favor of expressions defined in breakpoints with an automatic continuation.

Can't get factory or service to be recognized in controller with angluarjs using angular-seed

service controller angularjs factory

I keep getting "undefined is not a function" when calling the factory. I am using the angular-seed as the framework for my setup. For some reason, it doesn't recognize "GetFormVals" as a valid fact...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.