DATETIMEDIFF in 'hours' produces a negative numbers

[WayneWooldridge]

You may already be aware of a function that allows you to find the difference between two dates. It's the DATETIMEDIFF() function and you can use it in any tool that allows you to write an expression (Action, Condition, Filter, Formula, Generate Rows, Multi Field Formula, Multi Row Formula, Charting, Report Map, Table, Message, Test). The general syntax is DATETIMEDIFF([DateTime1], [DateTime2], 'units') where 'units' can be 'years', 'months', 'days', 'hours', 'minutes' or 'seconds'. But some of these smaller units - specifically, hours, minutes and seconds - can produce unexpected results.

The DATETIMEDIFF() function uses the data type Int32 as it calculates the difference between. In the case of smaller units of time - hours, minutes and seconds - the data is converted to seconds behind the scenes before it is converted to its final time unit. A difference between dates of 69 years gets converted into 2,177,474,400 seconds, which is too large for an Int32 data type. Data gets truncated and "wrapped around" to a variety of incorrect conversions, including negative numbers.

A recent case involving a DATETIMEDIFF() expression was attempting to determine the difference between date of birth and a date with the last year and the desired units was in hours. Notice the negative hours in the last two rows.

To get around this problem, hours can be calculated first by calculating age in days, then multiply by 24:

DATETIMEDIFF("2013-12-31",[CUST_DOB],"days")*24

Use a similar strategy when calculating minutes and seconds. Attached is an example that illustrates this concept. This example has the added benefit of showing how to get date fields into the correct format for the DATETIMEDIFF() function to work correctly.