Date vs DateTime comparison problem

[gregh]

If I use the type 'date' in a formula, with a comparison eg
[Duedate] < datetimeadd( [Quarter] , 3, 'months')   // where [Duedate] and [Quarter] are both 'date' types
this doesn't behave as expected for eg

Quarter	Duedate
2016-01-01	2016-04-01
2016-01-01	2016-04-02

-> neither of these are due in the quarter, but if the fields have type 'date' , the top one will evaluate as 'true'

whereas if they have type= 'datetime', they will both be false.

also if I use the following formula then it will be correct: datetimetrim([Duedate],'day') < datetimeadd( [Quarter] , 3, 'months') 

why is this? (it causes unexpected behaviour and is very hard to find the cause) 

[jdunkerley79 · Accepted solution]

DateTiemAdd Returns a datetime rather than a date

the easiest is fix is

Left(datetimeadd( [Quarter] , 3, 'months'), 10)

Alteryx treats dates and datetimes as strings so this will make the datetimeadd appear to have returned a date as well

[MarqueeCrew]

@jdunkerley79,

Isn't that equivalent to defining your variable in the function with a DATE field type?

[jdunkerley79]

@MarqueeCrew Yes if you store in an intermediary step.

If working with dates with the datetime functions then you need to truncate for comparison with dates

The DateTimeTrim function is silently converting the date to a datetime (i.e. appending 00:00:00 to the end of the date string).

[MarqueeCrew]

I agree.  I will post a generic "idea" in the community to simplify dates and their functions  I think that this is an area that can be improved for "ease of use".

[jdunkerley79]

Built my own :) 

https://github.com/jdunkerley/AlteryxFormulaAddOns

Will expand to have dateadd (instead of DateTimeAdd) and a set of time function in next week or so