Datetimediff ()

[rishiraj3]

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 ?

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

Thanks in advance 

[Kenda · Accepted solution]

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)

[jdunkerley79]

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

IIF(DateTimeYear(DateTimeNow())-DateTimeYear([PO_CREATION_TIME])<=1,1,0)

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

[rishiraj3]

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

[rishiraj3]

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 .

[rishiraj3]

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

[rohanonline]

@rishiraj3

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.

[Kenda]

@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 

datetimediff(datetimetoday(),[Field1],"years")

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!