Problems with Date Time Difference

[eswartz]

The following function results in the date difference of 0. I would expect that this would result in a date difference of 1. What is causing this issue? This seems to work with any other month difference with the exception of January and February month ends. These hard coded dates shown here are replaced with formulas in the actual data that I am working with. 

datetimediff("2017-02-28","2017-01-31","months")

[MarqueeCrew]

Did you try a data type of int16?

[jrgo · Accepted solution]

@eswartz,

It's because of how that function compares the two date values to output the difference. Image below is from help site and looking at the part highlighted, the day on the end date (first value) has to pass the start date. Since this won't happen in Feb, the month won't add until it goes to the next day.

 

One way that you can ensure that month diffs are always progressing is to trim your date values to the first of the month. Below is a formula that would give you the output you're looking for.

DATETIMEDIFF(DATETIMETRIM("2017-02-28",'firstofmonth'),DATETIMETRIM("2017-01-31",'firstofmonth'),"month")

Hope this helps!

Jimmy

[eswartz · Accepted solution]

This fixed the problem. Thanks!