asked on

T-SQL: Trying to Compute Difference Between Months

Hello:

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

Help!

TBSupport

Jerry_Justice

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 :

DateAdd(m,1,DateField)

it returns 10/30/2013

sventhan

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)

ASKER CERTIFIED SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TBSupport

ASKER

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)

TBSupport