community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Datetimediff ()

Highlighted
Meteoroid

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 

Pulsar
Pulsar

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:

 

 

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

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

 

 

Meteoroid

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

Meteoroid

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 .

Meteoroid

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

 

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

Pulsar
Pulsar

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

Labels