DateTimeAdd not providing the last day of the month

[IJH34]

Does anyone know why my datetimeadd formula is not giving me 8/31/2018 as the last day of the previous month? I can't figure this out! 

(DateTimeAdd(DateTimeLastofMonth(), -1, "months")) is giving me 2018-08-30 23:59:59

[Kenda]

Hi @IJH34!

This is happening because you are first getting the date/time of the last of this month (9-30-2018) and THEN subtracting one month, resulting in 8-30-2018.

[Kenda · Accepted solution]

@IJH34 To get the last day of the previous month, I tend to use this formula:

datetimeadd(DateTimeFirstOfMonth(),-1,"days")

 This will get you the first day of the current month then subtracts one day to result in the last day of the previous month. Hope this helps!

[IJH34 · Accepted solution]

Ah! So the best way to get the very last day regardless if its the 28th, 30th, or 31st would be DateTimeAdd(DateTimeFirstOfMonth(), -1, "Days")

[IJH34]

Perfect! Thank you for your help.

[KylePeterson]

I had the exact same issue going on.  Thanks @Kenda for this, definitely what I was looking for!

[Kenda]

Great! Glad you were able to find what you were looking for here 🙂

[ChrisTX]

DateTimeTrim is also helpful to get the last day of the month:

DateTimeTrim(dt,t): Remove unwanted portions of a DateTime and return the modified DateTime

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

t: Trim type. Options include:

• firstofmonth:  trim to the beginning of the month (this does the same as month)
• lastofmonth: extend to one second before the end of the last day of the month