Link to home
Start Free TrialLog in
Avatar of countrymeister
countrymeister

asked on

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

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.
Avatar of yawkey13
yawkey13
Flag of United States of America image

WHERE DATEDIFF(m, paramDate, GETDATE()) = 0
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@matthewspatrick: Why is the extra math and extra comparisons necessary?  Would using datediff alone, passing it month as the unit, not suffice?
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'

select 
	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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Makes sense.  Thanks.  Glad I asked.
Avatar of countrymeister
countrymeister

ASKER

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