Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Problems with Date Time Difference

Highlighted
5 - Atom

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")

Alteryx Certified Partner
Alteryx Certified Partner
Did you try a data type of int16?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner

@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.

image.png 

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

Highlighted
5 - Atom

This fixed the problem. Thanks!

Labels