How does DateDiff() work when calculating months in Spotfire

[Sean Tabor]

Hi,

 

I am trying to understand how DateDiff() is calculating the values I am seeing. For example, the following custom expressions give the cprresponding values:

DateDiff("month", Date("01-Jan-2019"), Date("01-Feb-2019")) --> 1.0035

 

DateDiff("month", Date("01-Jan-2019"), Date("02-Feb-2019")) --> 1.0392

Could someone please explain how Spotfire is calculating the fractional part of the number of months

i.e. for the above I would've expected:

--> 1.0000

--> 1 + 1/28 = 1 + 0.0357 = 1.0357

 

Any insights would be much appreciated!

[Khushboo Rabadia]

That is generic way datediff function works not just in Spotfire.

DateDiff("month", Date("01-Jan-2019"), Date("01-Feb-2019")) does not give you exact 1 month difference.This is a purely mathematical month-fraction calculation that, for both the start-date month and the end-month month, depends on the number of days in their respective months.

This subtraction technique produces month-difference result between 1/1/2019 and 1/2/2019 as 1.003456 (not 1.000) since the difference between the fractional parts is (1/28)-(1/31) = 0.003456

Example discussion in this thread:

https://www.access-programmers.co.uk/forums/threads/datediff-fractional-...