This seems like a pretty simple and common requirement but the truth is, it is a common requirement but it is simple only if you know the solution.
You have a Start date and an End date. You need to find number of months between the two dates.
We do not exclude partial months or in other words we are not considering number of days in a month. Below are few examples to help in understanding what we are trying to achieve
Start Date : 01/01/2011 End Date : 03/15/2011 :: Number of months = 3
Start Date : 01/28/2011 End Date : 02/01/2011 :: Number of months = 2
Start Date : 01/01/2011 End Date : 01/15/2011 :: Number of months = 1
The Declarative solution is pretty simple and can be used anywhere Siebel supports expressions i.e Calculated Fields, Workflows, Runtime Events, Data Validation Manager, EAI Data Maps etc. I am providing solution by way of Calculated Field but the same expression can by used on any of the objects mentioned above
Calculated Field Expression :
JulianMonth([End Date]) - JulianMonth([Start Date]) + 1
Sometimes scripting is the only available interface for you (business service supporting integration) and it is not possible to use declarative solution. In that case the function JulianMonth is not available to you. You can use following line of code to accomplish the same
var numOfMonth = (endDate.getYear() - startDate.getYear()) * 12 + endDate.getMonth() - startDate.getMonth() + 1 ;
I will post the solution to only consider whole months depending on the interest. Let me know by way of comments if you would like to see the solution to exclude partial months.
Your feedback is welcome as always.