Product: TIBCO Spotfire®
Why does my DateDiff column show a negative number?
When creating a calculated column using DateDiff the resulting value is negative when the Stop Date is after the Start Date. It's expected that this is going forward in time, so a positive number is expected.
This is by design. The Help files for DateDiff state:
Calculates the difference between two Date, Time or DateTime columns. The result is presented either as a TimeSpan or as an integer representing a specified time part (e.g., number of days). If two arguments are used (a start date column and a stop date column) the result will be a TimeSpan value
displaying the total difference.
When DateDiff returns a TimeSpan (two arguments) the results are shown as a negative, which is the standard procedure for most database systems, so Spotfire follows suit. When you add a third argument to specify what part of the dates to compare, i.e. "days", DateDiff returns a positive integer showing the exact numbers of days difference.