SQL - Proper “where” clause for current month

Which is the proper way of checking events from current month on SQL Server and why?

1) WHERE (DATEDIFF(month, EventTime, GETDATE())=0))

2) WHERE (YEAR(EventTime) = YEAR(GETDATE()) AND MONTH(EventTime) = MONTH(GETDATE()))

Date format in table is i.e. EventTime: 2011-11-30 15:68:25.000

Answers


I don't have access to a SQL Server with a profiler, so I can't actually give as detailed an answer as I'd like.

The question is basically about which one allows the least calculation and most effective use of indexes.

Variants that use string manipulation have the highest calculation load, and don't use indexes at all. So I'll just skip those. That leave four common expressions...

SELECT * FROM date_sargable
WHERE  YEAR(value) = YEAR (getDate())
  AND MONTH(value) = MONTH(getDate())
;

SELECT * FROM date_sargable
WHERE DATEDIFF(MONTH, value, getDate()) = 0
;

SELECT * FROM date_sargable
WHERE DATEDIFF(MONTH, 0, value) = DATEDIFF(MONTH, 0, getDate())
;


SELECT * FROM date_sargable
WHERE value >= DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate())    , 0)
  AND value <  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()) + 1, 0)
;

The first three use INDEX SCANs, but the last one uses an INDEX SEEK. The difference is that the format of the query allows the optimiser to know you want a specific range of the data, that it's all next to each other in one block of the index, and that it's very easy to find that block.

If, when looking at execution plans, you see a SEEK in one version, and a SCAN in another, you're much more likely to benefit from the SEEK.


Need Your Help

Smart ways to determine if a search request is an address or a business for use in Google APIS?

string google-maps geocoding google-local-search

Here is the problem, I have an app with a search bar, the user can input something like "18th Street" or "Starbucks" and it uses the Google Geocoding and Local Search APIs respectively to get resul...

post variables and label fields - UPDATED

php forms

I was wondering, If when sending a form via PHP i.e.

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.