I was using the DateDifferenceCalculator to work out the difference in days between a completed date (contains date and time) and now. This is to find out if the completed date is in the past 30 days. I have now converted this to a SQL query using the DateDiff function: SELECT DATEDIFF(day, completed_date, CURRENT_TIMESTAMP) from ...
The SQL function returns different results to the DateDifferenceCalculator, often 1 day less. For example DateDifferenceCalulator says the difference between today (14th September) and a completed date of 17th August is 29, but SQL Server (and Excel) say 28. To be precise, the completed date is 17/08/2018 00:32:00 and the comparison was done at about 14/09/2018 17:20:00. Excel give the difference as 28.7 which I would round down to 28 because it's not yet been 29 days.
Looking at the embedded transformer, it appears that DateDifferenceCalculator is rounding up the output value if the decimal point value is greater or equal to .5. SQL Server DateDiff function is a bit more complicated, because as this StackOverflow topic says, "DATEDIFF uses the boundary of the date part. So for "day", 23:59 today is one whole day before 00:01 tomorrow".
https://stackoverflow.com/questions/5131958/datediff-rounding
For example:
select DATEDIFF(DAY, '2018-09-14 23:59:00', '2018-09-15 00:01:00')
= 1
To get a more precise value, you have to get the hours, then divide by 24:
select (convert(numeric(10,3), DATEDIFF(HOUR, '2018-09-14 23:59:00', '2018-09-15 00:01:00')))/24
= 0.04166 then you can round as you see fit.
DateDifferenceCalulator returns zero for this comparison which I would agree with.
So it seems neither method is completely perfect. Any thoughts?
