like captioned, I totally cannot understand this logic. I am expecting 2020-01-31, but it's not.
Solved! Go to Solution.
Hi @JokeFun
The result you get does seem a little odd, if I run it using Alteryx 2020.2 I get 2020-01-29 which is what I would expect as that is one month before 2020-02-29.
However, are you looking to get the end of the month before?
If so, the best option is to do:
DateTimeAdd(DateTimeTrim("2020-02-29","month"),-1,"day")
The DateTimeTrim will return the first day of the month, then you minus one day to get the last day of the month before.
Hi @JokeFun , try this, I hope this will work.
Formula: Datetimeformat(DateTimeAdd(DateTimeTrim(field1,"month"),-1,"day"),"%Y-%m-%d")
Thanks.
Thank you all. I think the help document is a little bit misleading, or at least not details enough.
I was taking such example as saying this formula can get the month end date.
"DateTimeAdd("2016-03-30", -1, "month")returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th)"
@JoeS You are right, I am trying to get the month end date of last month. Your solution is a nice one, thanks!
@JokeFun wrote:Thank you all. I think the help document is a little bit misleading, or at least not details enough.
I was taking such example as saying this formula can get the month end date.
"DateTimeAdd("2016-03-30", -1, "month")returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th)"
@JoeS You are right, I am trying to get the month end date of last month. Your solution is a nice one, thanks!
I can see where the confusion may have come from. I think the example was trying to explain that the formula is smart enough to know that the 30th wouldn't be a valid date so shifts it to the highest valid date, but can see why that's misleading.
Glad my expression worked for you though.