Is something wrong with the datetimediff function

[paolo_carbone]

I have been using the following function

DateTimeDiff("2013-12-31",[CUST_DOB],"hours") to calculate age

I need the time difference in hours in order to match the age with another app.

this is what I get:

CUST_DOB                            AGE             Age6 (in hrs)

1945-12-13 00:00:00               68             596520

1946-12-02 00:00:00               67              588024

1900-01-01 00:00:00            114            -193758

1945-07-22 00:00:00               68            -593070

1945-12-12 00:00:00               68            -596502

Why am I getting negative hours in some of my records (records older than 69 ) and in others not .

Does alterix have some date-time baseline that I need to set? 
Thanks for your help

[ChadM · Accepted solution]

Hi Paolo,

The DateTimeDiff() function utilizes the Int32 data type on the back end and calculates hours and tries to take into account 'seconds' as well.  Because of this, selecting 69 years in hours first gets calculated to 2,177,474,400 seconds, which is too large for an Int32 so it gets "wrapped around" to a negative number.

We have logged a bug for this, so it will be resolved in a future release.  In the meantime, you can calculate hours by first calculating the age in days, then muliply by 24: 

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

Since you do not have the hours listed in your first date of 2013-12-31, this should work nicely.

Thank you!

Chad