community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

DATETIMEDIFF in 'hours' produces a negative numbers

Alteryx
Alteryx
Created on

 

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. 

 

DATETIMEDIFF1.png

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.

Attachments