Siebel CRM - Project 12 (Julian Day in BS, absolute date difference in days)

• Report rights infringement
• published: 20 Apr 2015
• views: 249

Siebel CRM - Project 12 (Julian Day in BS, absolute date difference in days) 17/4/2015 *****WARNING***** This video is all about testing the results of my code which is a slow process (to say the least, bloody data entry!) and probably way too boring for most of you guys and gals so you might as well want to skip it.. *****WARNING***** This is more of a JavaScript than a Siebel “riddle” but here goes... Long story short, I needed a mechanism that accurately calculates the absolute difference between two dates in days. The result should be a positive whole number (integer) that represents the date interval in days. For instance, in a programming language called Guru (or Knowledgeman) we can achieve that just by using a built-in function to convert each of these dates to Julian days, subtract the results and use the absolute function (abs(tojul(Date1)-tojul(Date2))). We can do exactly the same thing in Excel using the datevalue (as long as the dates are after 1/1/1900) and abs functions. The results will look like this: Date1------------Date2-----------Difference in days 1/1/2000--------1/1/2000-------------0 1/1/2000 --------15/6/2000----------166 20/2/2000-------15/1/2004----------1425 19/4/2004-------20/2/2000----------1520 1/1/2000---------15/4/2000----------105 1/1/2000 ---------18/6/2004----------1630 1/1/2000---------22/9/2008-----------3187 1/1/2000---------18/4/2015-----------5586 1/1/1901---------18/4/2015-----------41745 18/6/2004-------18/6/2014-----------3652 18/6/2004-------1/2/2012-------------2784 18/9/2004-------30/4/2012-----------2781 This doesn’t seem to be a very straight forward task in JavaScript. Daylight saving time, time zones, calculations in milliseconds and rounding operations seem to cause inconsistencies. If you see JavaScript code that is supposed to do this but contains rounding operations (ceil, floor etc.) and the classic milliseconds/minutes/hours/days transformation operations then double and triple check the results using a variety of date pairs. I found quite a few JavaScript algorithms on the internet but to my surprise they'd calculate correctly the difference in days for some date ranges while they'd fail to do so for others. Siebel provides us with Julian functions so depending on "where" we are (calculated field, workflow etc.) and what we want to do we can either use these functions or even directly subtract two dates, add days to a date etc. But as far as I know we can’t access this functionality within a Business Service (or script in general), at least not directly (but who wants to keep invoking a workflow within a BS again and again just to do that, right?). So I created two new methods (functions) in one of my custom Business Services (although chances are I’ll just modify the code a bit, put it in a JS file and #include it whenever I need to use these functions): JulianDay(): this method contains the algorithm that converts a passed date to Julian Day. It always returns a positive integer (unless the date is BC but personally I’m more interested in days after 1/1/1900). All date format validations take place in this method. DateDiff(): it accepts two dates, calls JulianDay() for each of them and returns the absolute difference of the results which represents the actual date interval in days. Both methods work with "DD/MM/YYYY" date format but it's very easy to make them work with pretty much any date format (if needed). Let’s test them… I wrote a very simple routine in Guru that calculates these date differences (using the built-in functions) so we’ll use these results as a reference point. I also wrote a small Java application that does the same thing but using my algorithm. We won’t bother testing the previous 12 sets of dates as I’ve already cross-checked the results with Guru, Excel, Java and Siebel (JS). Before we start testing you should keep two things in mind: - The date format is "DD/MM/YYYY" - We don't care about the order of the dates so it doesn't matter which one is Date1 or Date2, the results will be exactly the same. We’ll get ten random pairs of dates from random.org and test the algorithm. Let’s test dates from 1/1/1900 to 31/12/2200. Guru’s, Java’s and Siebel’s results should match.