CRM, SSIS & Integrations ...configuring CRM as a non-coder
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
CRM, SSIS & Integrations
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
  • Home
  • CRM
  • SSIS
  • How-to
  • JavaScript
  • SQL
  • Difference between two dates (SQL, SSIS and JavaScript)

Difference between two dates (SQL, SSIS and JavaScript)

May 4, 2020 Leave a Comment Written by Thomas

<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
  }
}
CRM, How-to, JavaScript, SQL, SSIS
Data, Dynamics365, How To, JavaScript, SQL
Easy way to fetch row count for all tables in a SQL database

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

The author

KingswaySoft MVP & Microsoft certified professional

My name is Thomas and I am a consultant at a company called netcompany A/S in Denmark. My primary focus is working with SSIS and KingswaySoft, where I perform integrations and datatranformations.

Click for more info

Recent Posts

  • Difference between two dates (SQL, SSIS and JavaScript)
  • Easy way to fetch row count for all tables in a SQL database
  • Create Application user in Azure for use in Dynamics 365
  • SSIS Basics: Merge join
  • Open dashboards in a new window

Useful information

  • Installation guide to KingswaySoft
  • Software download list
  • CRM Object Type Codes
  • CRM State & Status codes
  • Script Repository

Archives

  • May 2020 (1)
  • February 2020 (1)
  • January 2020 (2)
  • August 2019 (1)
  • May 2019 (1)
  • February 2019 (2)
  • January 2019 (2)
  • November 2018 (1)
  • September 2018 (1)
  • August 2018 (3)
  • May 2018 (4)
  • December 2017 (2)
  • August 2017 (1)
  • February 2017 (1)
  • November 2016 (4)
  • October 2016 (1)
  • September 2016 (2)
  • August 2016 (5)
  • June 2016 (4)
  • May 2016 (3)

Useful blogs/links

Henrik Jensen, my good friend and colleague, has a blog regarding EVERYTHING Dynamics crm. He's got 50+ certifications, and he is a force to be reckoned with. Link to his blog (danish): crmblog.dk

KingswaySoft have developed an outstanding piece of software for datamanagement to and from CRM, and I can recommend this toolkit for nearly every situation. Link to their website: kingswaysoft.com

Tag Cloud

Azure Bug CRM 2015 CRM 2016 CRM Online Data Dynamics365 ERP Excel How To JavaScript KingswaySoft Log News Office Outlook Personal Solution SQL SSIS Visual Studio

Categories

  • Azure (2)
  • Clubtimiser A/S (1)
  • CRM (30)
  • ERP (2)
  • How-to (14)
  • JavaScript (3)
  • Office (3)
  • Personal (4)
  • SQL (2)
  • Visual Studio (23)
    • SSIS (23)
      • KingswaySoft (14)

Disclaimer

The information given on the website is tested on a mixture of live production environments and test environments, and therefore I cannot guarantee my information will work for you, if you attempt to follow my steps.
If you experience problems, please feel free to contact me.

@June 7th 2016