Jump to content
We've recently updated our Privacy Statement, available here. ×

How does DateDiff() work when calculating months in Spotfire


Sean Tabor

Recommended Posts

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!

Link to comment
Share on other sites

  • 2 weeks later...

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-...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...