# 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
``````

I need to add a third calculated field:
``````[Contract_NetNumberOfPeriods]
``````

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.
###### Who is Participating?

DATEDIFF(MONTH, ContractStartDate, ContractEndDate)

This depends on how you want to calculate the number of months.  In the case above...

DATEDIFF(MONTH, '1/31/2014', '2/1/2014') = 1
0

Senior DBACommented:
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"?
0

Commented:
if you want to use original table columns you can use.

SELECT
[Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END
,Contract_NetNumberOfPeriods =  datediff(m,CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END,CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END)

``````
``````
0

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

Commented:
you are right, it is always good to avoid duplication. Another solution could be to wrap in outer query like this:

``````SELECT *,
datediff(m,Contract_NetStartDate , Contract_NetEndDate ) as Contract_NetNumberOfPeriods
FROM
(
SELECT
[Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END

) x
``````
0

Author Commented:
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

??
0

Commented:
For syntax, see here -
http://www.w3schools.com/sql/func_datediff.asp
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.