asked on

get the number of months between start and end dates

Hello.

I have to calculated fields:

,[Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END

Open in new window

I need to add a third calculated field:

[Contract_NetNumberOfPeriods]

Open in new window

The third field is the number of months between [Contract_NetStartDate] and [Contract_NetEndDate]

Is it possible that some one show me how to obtain that number from the two fields?

Thank you much.

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

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

Start Free Trial

Scott Pletcher

Do you want to do the calculation on the original table columns (OR) on the results of the CASE expressions?

What do you mean by "month"? That it crossed a calendar month, or that it was a minimum number of days for each "month"?

SOLUTION

cshark247

membership

This solution is only available to members.

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

Start Free Trial

Scott Pletcher

I wouldn't repeat the expressions twice, too hard to keep in sync later.

SOLUTION

cshark247

membership

This solution is only available to members.

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

Start Free Trial

metropia

ASKER

Thank you experts.

I have a question, why when I use:

DATEDIFF(m,Contract_NetStartDate , Contract_NetEndDate)

I get negative numbers, but if I switch to:

DATEDIFF(m, Contract_NetEndDate, Contract_NetStartDate)

Then the number of months is positive

??

awking00

Because your Contract_NetEndDate must be earlier than your Contract_NetStartDate.
For syntax, see here -
http://www.w3schools.com/sql/func_datediff.asp