What is the most efficient way to check if a date has the current month and year in sql

countrymeister used Ask the Experts™
I have a list of dates that are passed to a stored procedure.

I have to check if the date passed in has the current month  and current year.
Please note the list could contain over 2000 rows, so performance is important.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

WHERE DATEDIFF(m, paramDate, GETDATE()) = 0
Top Expert 2010
If the dates are in a table, then use a WHERE clause like this:

WHERE TheDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND
    TheDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)

Open in new window

@matthewspatrick: Why is the extra math and extra comparisons necessary?  Would using datediff alone, passing it month as the unit, not suffice?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I ran the following to make sure  I wasn't oversimplifying the problem.  datediff(m,@d1,getdate)  returns 0 as expecting causing my where clause about to evaluate true.  The other two evaluate to false, as expected.

declare @d1 as datetime
declare @d2 as datetime
declare @d3 as datetime

set @d1 = '05/11/2012'
set @d2 = '05/01/2011'
set @d3 = '06/01/2012'

	datediff(m,@d1,getdate()),  --same month & year
	datediff(m,@d2,getdate()), --same month, different year
	datediff(m,@d3,getdate()) --different month, same year

Open in new window

Top Expert 2010

You are of course correct that a simpler WHERE clause will work:

WHERE DATEDIFF(month, TheDate, GETDATE()) = 0

Open in new window

I do not recommend that approach, however, because that particular test will not be able to leverage any indexes on that table.  I prefer the approach I used because if that date column is indexed, my expression can make good use of it.



Makes sense.  Thanks.  Glad I asked.


Thank you both, in my case the date was not indexed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial