DATETIMEADD function Unexpected result

[Mario_Borrelli]

Wondering if the DateTimeAdd function has a bug.  Consider the following:

DATE_X = '2017-12-08' converted to date using DateTimeParse([DATE_X],"%y-%m-%d")
DATE_Y = '2017-12-15' converted to date using DateTimeParse([DATE_Y],"%y-%m-%d")

My test is whether DATE_Y less than 7 days from DATE_X.  Logically, the answer should be 'No' since DATE_Y is exactly seven days after DATE_X.

If however I use a formula below, the answer is Yes:

IF [DATE_Y]< DateTimeAdd([DATE_X], 7,'days') THEN 'Yes'
ELSE 'No'
ENDIF

If I create a variable and set the formula to that variable, I get the expected 'No':
[DATE_Z] = DateTimeAdd([DATE_X], 7,'days')

IF [DATE_Y]< [DATE_Z] THEN 'Yes'
ELSE 'No'
ENDIF

[vishwa_0308]

Hi @Mario_Borrelli,

I would suggest you to simply use this "DateTimeDiff()" function as below: 

if DateTimeDiff([Date_Y],[Date_X],"days")<7 then "No" else "Yes" endif

Best,

Vishwa

[Mario_Borrelli]

Thanks Vishwa

I already had a workaround.  Was more interested in why this was happening.

[MSalvage · Accepted solution]

@Mario_Borrelli,

I think this has to be a type issue. When I did the same calculation with the two dates I got yes for both variable and normal. When I changed them to datetimes I got 'No' for both variable and normal. Apparently '2017-12-15 00:00:00' is greater than '2017-12-15'. See output below.

With Dates:

With DateTimes:

I don't see how you would ever get 2 different answers I would double check you  didn't somehow convert your Date Y to Date time in your variable expression test.

Best,

MSalvage

[Mario_Borrelli]

I think that hits close to the answer. 

I am using Alteryx 10.5 and there are no DATEADD functions, only DATETIMEADD which returns a DATETIME type. 

Since my variables are both DATE type and since it appears that 2017-12-15 does not equal 2017-12-15 00:00:00, we are not really comparing like for like, even though logically they are the same.

Thanks.