Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!

Datetimediff ()


Hi ,

I am using below logic to get the desired result(data for 2016 and 2017 only) , but alteryx includes  Nov-2015 and Dec-2015  records as well . I am not sure why it includes this . does alteryx convert first in months then convert it into years .Can anyone help me out on this ?

"years")<=1 AND [PO_CREATION_TIME] <=DateTimeNow(),1,0)


Thanks in advance 


Hey @rishiraj3! If you are just wanting the records from 2016 and 2017 only, try this formula:


iif(ToNumber(Left(DateTimeNow(), 4))-ToNumber(Left([PO_CREATION_TIME], 4))<=1 && [PO_CREATION_TIME]<=DateTimeNow(),1,0)

Just to call out the new function in v11.5:




If you have an older version of Alteryx then this won't work




Thanks  for your help , it is working as per expectation .


Thanks for sharing information but currently I am using version 11.0.5 and as you said mentioned function is not available in this version .


Do you have any idea how does datetimediff() work ? Does it convert first in days, months and then show the data in years ?



There are 3 parameters used in datetimediff(<end date>,<start date>, <"units">)... the third parameter will help you find the difference in either days, months or years. So if you enter

- datetimediff("2017-01-01","2016-01-01","years") - the answer would be 1

- datetimediff("2017-01-01","2016-01-01","months") - the answer would be 12

- datetimediff("2017-01-01","2016-01-01","days") - the answer would be 365


Hope that helps.


@rishiraj3 It seems like that is how it is working (using the specific day to find the difference then converting to years for output).


For example, Alteryx knows that October 13th has not occurred yet in 2017, so if 2016-10-13 is your starting date and you are using the formula 


then you get 0 because it has not been a full year since your start date. 


If, instead, 2016-10-07 is your start date and you use the above formula, you will get 1 for your output because it has been a full year between that start date and today.



Hope this helps!