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?
sventhanConnect With a Mentor Commented:
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)
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)
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)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.