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)
Solved! Go to 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
Isn't that equivalent to defining your variable in the function with a DATE field type?
@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).
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".
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