Difference between two dates (SQL, SSIS and JavaScript)

<START Disclaimer>
The below methods and scripts are not the cleanest in the world, but they work – which is many cases are the important bit. Each of them can be cleaned a bit up, and made simpler.
</ END Disclaimer>

When I work with contracts, accruals, forecasts etc, I often have to calculate the duration of the contract, or in general the difference between two dates. I have been using an ordinary datediff in Months, as this is the fastest way, to get the duration of a contract in months.

It could look something like this:

SQL:
DATEDIFF(mm,activefrom,activeto)+1

SSIS:
DATEDIFF("mm",activefrom,activeto)+1

However, the month datediff, doesnt take crooked dates into account. In the case of Dynamics365, the datefields also have a timestamp, and depending on the timezone, a date like 01/01-2020 00:00:00 will become 31/12-2019 22:00:00 in SSIS, and mess up the month datediff.

The quick fix to this, is simply to round the date to the nearest 00:00:00 using:

SQL
CAST(ROUND(CAST([date_field] as float),0) as datetime)

SSIS:
(DT_DBTIMESTAMP)ROUND((DT_R4)(DT_DATE)[date_field],0)

This will take care of timezones mismatch, but it will not take care of dates, which are meant to be crooked. If a contract has a start of 01/01-2020 and ends on 01/01-2021, the month datediff will calculate that as 13 months, even though its actually 12.

And a contract running from 01/01-2020 till 20/06-2020 will count 6 months, and not 7.

To “fix” all the issues with crooked durations, I changed to calculation from month datediff to days datediff.

Example 01 (SQL):
activeFrom: 01/01-2021
activeTo: 31/12-2021

datediff(dd,activefrom,activeto)+1 will give me 365 days

To get the months, I need to divide the number, with the average number of days in a month. The calculation looks something like this:
(((365+365+365+366)/4)/12) = 30,4375
First, I find the average number of days in a year (including leap year), and I divide the number with 12. I will take the result, and divide my datediff with that:

(datediff(dd,activefrom,activeto)+1) / 30.4375 = 11.9917

This is the actual duration based on the average number of days. I will need to round this to the nearest integer, to get a clean number.

ROUND((datediff(dd,activeFrom,activeTo)+1) / 30.4375,0) = 12

However, if for some reason, the two dates are LESS than 15 days apart, the result will be 0. We need to take that into account. The easiest way to do this, is with an if statement.

IIF(ROUND((datediff(dd,activeFrom,activeTo)+1) / 30.4375,0)=0,1,ROUND((datediff(dd,activeFrom,activeTo)+1) / 30.4375,0))

With the above IIF, it will return a duration of 1, when it returns a 0.

The following is my syntax for the duration in SQL, SSIS and JavaScript:

SQL:
IIF(ROUND((datediff(dd,activeFrom,activeTo)+1) / 30.4375,0)=0,1,ROUND((datediff(dd,activeFrom,activeTo)+1) / 30.4375,0))

SSIS:
(DT_I4)(ROUND(((DATEDIFF("dd",activeFrom,activeTo) + 1) / 30.4375),0)) == 0 ? 1 : (DT_I4)(ROUND(((DATEDIFF("dd",activeFrom,activeTo) + 1) / 30,4375),0))

JavaScript:
function contractDuration(executioncontext) {
  var formContext = executioncontext.getFormContext();
  var daysInMonth = (((365 + 365 + 365 + 366) / 4) / 12);
  var activeFrom = formContext.getAttribute("dyn_activefrom").getValue();
  var activeTo = formContext.getAttribute("dyn_activeto").getValue();
if (activeFrom != null && activeTo != null) {
  var diff = activeTo - activeFrom;
  var calcDiff = ((diff / (1000 * 60 * 60 * 24)) + 1) / daysInMonth;
  var roundDiff = Math.round(calcDiff);
  alert(roundDiff); // Map roundDiff into the desired field
  } else {
  //nothing
  }
}