Avatar of 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.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

WHERE DATEDIFF(m, paramDate, GETDATE()) = 0
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

@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'

	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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Makes sense.  Thanks.  Glad I asked.

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