Problem with Date Operations

[Arjun]

Hi

I am trying to create a dynamic formula with dates involved but am getting something unexpected.

I have a date column (say Baseline Date) and the operation I am using on it is - [Baseline Date] < DateTimeAdd(DateTimeFirstOfMonth(), -1, "months"). So what I am trying to do is to get all the dates before the 1st of December 2015.

The problem is that Alteryx is giving me an assertion (TRUE value) for when [Baseline Date] is 1st of December itself.

In another formula, I was using -  [Baseline Date] >= DateTimeAdd(DateTimeFirstOfMonth(), -1, "months") but that wasn't giving me the 1st of December. I realized that Alteryx might be accounting for an innate time in the dates as well so I changed my formula to - [Baseline Date] > DateTimeAdd(DateTimeLastOfMonth(), -2, "months") and it worked fine then.

I have checked that - DateTimeAdd(DateTimeFirstOfMonth(), -1, "months") - gives me 1 December 2015 00:00:00. Hence there should be no problem in the first operation. I have made sure that the format of the dates is correct and have parsed the dates as well.

Please help and let me know where I might be going wrong. Thanks!

[s_pichaipillai]

Hi Arjun,

are you trying to filter the data based on this condition 

can you post your sample workflow to investigate further ?

[jdunkerley79]

Interesting one.

I think if you store the DateTimeAdd(DateTimeFirstOfMonth(), -1, "months") as a Date field then use as a bool condition.

I am guessing this because Alteryx is treating the output of DateTimeAdd as a string of '2015-12-01 00:00:00' which is then put in an Alpha compare with the BaseLine date.

Attached workflow showing what I mean

[michael_treadwell · Accepted solution]

Arjun, I was able to replicate your results.

So for the formula: [Baseline Date] < DATETIMEADD(DATETIMEFIRSTOFMONTH(), -1, 'months')

As of today, DATETIMEADD(DATETIMEFIRSTOFMONTH(), -1, 'months') will return value '2015-01-01 00:00:00'

When [Baseline Date] is a Date field of value '2015-01-01' the formula returns TRUE

When [Baseline Date] is a DateTime field of value '2015-01-01 00:00:00' the formula returns FALSE

I can't say that I expected this behavior but it obviously has something to do with the time element. My recommendation would be to convert [Baseline Date] to a DateTime field so that you get the results you expect.

I've attached a module to show the two different result sets.

[Arjun]

I was not trying to filter the data - the condition is part of a large formula that performed other functions. The solutioin provided by Michael solves it. :)

[Arjun]

Thanks for the post Michael - It did the trick! But I guess this could be something that Alteryx can improve. I did not convert my date to DateTime thinking that whatever be the case, the time portion of the Baseline would be at least as much or more than 00:00:00.

I really appreciate your help on the question! :)