Comparing Dates

[treehook]

Hi,

Apologies for the beginner question. I'm trying to compare three dates (ensure one date is between two other dates). All dates are in the format YYYY-MM-DD and have selected type as Date when i joined the two tables. However i keep getting an error message when i try to use a simple '<' or '>' sign. I also tried using DateTimeDiff function to just get the days diff as positive or negative as the test function. regardless i keep getting an error and it could have something to do with the If statement that wraps around it. 

Would anyone mind posting the syntax to get this done?

Thanks!

[RodL · Accepted solution]

A common mistake is to use a "Date1 > Date2 > Date3" syntax. That won't work in Alteryx.

This should work...

IF [Date2] > [Date1] AND [Date2] < [Date3] THEN 1 ELSE 0 ENDIF

[treehook]

Thanks Rod. it worked perfectly. Appreciate you taking the time to help out the beginners.

[iberlin]

I didn't work for me because I need to compare to a specific  date " 2016-12-01".

Could you please suggest the best way to do it?

Thanks,

Irina

[RussellD]

Hi Irina,

If you want to change Rod's solution to include a specific date that is not already in a field then you should look to use the formula tool and the DateTimeParse Function. 

To combine Rod's Solution and your question would look like this. Replacing [Date3] with "2016-12-01"

IF [Date2] > [Date1] AND [Date2] < (DateTimeParse("2016-12-01","%Y-%m-%d")) THEN 1 ELSE 0 ENDIF

There are many community articles on using DateTimeParse that may help, also the Help on DateTime functions  this shows things like other specifiers (%Y, %m etc) and samples.

I'd also like to suggest adding this a field to you data as that will make it easier to reuse in formulas. One way would be a text input with "2016-12-01" which will read in as a date. Then Append Fields to add this to your data set.  The nice thing with this method is you only need to change the date in 1 text input box at the start to have that change through your workflow. 

[iberlin]

 Thank you very much for your help. This will help me with one issue, but I also have a case when I am trying to do date comparison to a specific date but need to know if Date1 – Date2 >=30.

There is no simple subtraction in functions, so I am guessing this has to be done with Datediff and IF statement.

Could you please assist?

Thanks a lot!

Irina

[RodL]

You can use DateTimeAdd with a negative interval (essentially subtracting).

[iberlin]

I am trying to create groups based on dates with nested IFs but everything is getting bucketed into the same group.

Any suggestions on how to do it?

Thanks,

Irina

IF [REG_CREATE_DATE] < (DateTimeParse("2016-01-10","%Y-%m-%d"))

THEN
'360 Plus Days'

ELSE
IF [REG_CREATE_DATE] >= (DateTimeParse("2016-07-10","%Y-%m-%d"))

THEN '180 Days'

ELSE
IF [REG_CREATE_DATE] >= (DateTimeParse("2016-12-10","%Y-%m-%d"))

THEN '30 Days'

ELSE
IF [REG_CREATE_DATE] >= (DateTimeParse("2017-01-03","%Y-%m-%d"))

THEN '7 Days'

ELSE ' ' 
ENDIF ENDIF ENDIF  ENDIF

[RodL]

ELSEIF statements are evaluated in the order they appear in the expression, so by doing the "360" statement first, it captures them all and leaves nothing else to evaluate.

Try reversing them by starting with the "7" statement first, then add "30", "180" and "360".

And as a matter of syntax, you only need one ENDIF, and I usually type "ELSEIF" together since it is really one word in the expression.