T-SQL: Trying to Compute Difference Between Months


I just want to take the current month of the document date and make it "1" and do the same for future months (i.e. 2 = next month, etc.) and say that those numbers are between 1 and 18.

Once again, I'm not getting it:

where DATEADD((DATEPART(m, getdate())))- DATEADD(DATEPART(m, POContractHeader.DOCDATE)) BETWEEN 1 and 18


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't think you understand the DateAdd function.

The DateAdd function returns a new date.

The first parameter is supposed to tell the function what part of the date you want to add to (or subtract from)

SQL Server DATEADD() Function

The second parameter is how much you want to add or subtract.

The third parameter is the actual date you want it to start with.

I have no idea what you are trying to accomplish by using the "DatePart" function, which returns a number.

Perhaps you were confused that the first parameter name in the DateAdd function is "DatePart"?  That is just a description, not telling you to use the DatePart function.

If you have a record where a Date field is 09/30/2013 and you add 1 month :


it returns 10/30/2013
Try something this ...

where o.doc_date >= dateadd(m, datediff (m, 0, @o.doc_date), 0)
  and o.doc_date < dateadd(m, datediff (m, 0, @o.doc_date) + 18, 0)
I mean

where doc_date >= dateadd(m, datediff (m, 0, @doc_date), 0)
  and doc_date < dateadd(m, datediff (m, 0, @doc_date) + 18, 0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
Hi sventhan:

I think that I got it right.  Here's what I used, based on your suggestion:

where POContractHeader.DOCDATE >= dateadd(m, datediff (m, 0, GETDATE()), 0)
  and POContractHeader.DOCDATE < dateadd(m, datediff (m, 0, GETDATE()) + 18, 0)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.