MONTHSTART Failure in Server

[knobsdog]

I have a workflow that's failing on Server b/c it uses the date function MONTHSTART.  Does anyone know of a way to use something similar that will run on Server?  Here's the function with generic field names if that helps.

IF DateTimeFormat(DateTimeToday(),"%d") > "15" and [scheduled_date]>= MONTHSTART (DateTimeToday()) and [scheduled_date]<=(DateTimeAdd(MONTHSTART(DateTimeToday()),+15,"days"))then "Keep"

elseif DateTimeFormat(DateTimeToday(),"%d") < "15" and [scheduled_date]>=

DateTimeFormat(DateTimeAdd(MONTHSTART(DateTimeAdd(MONTHSTART(DateTimeToday()),-1,"days")),+14,"days"),"%Y-%m-%d") and

[scheduled_date]<= DateTimeFormat( DateTimeAdd(MONTHSTART(DateTimeToday()),-1,"days"),"%Y-%m-%d") then "Keep"

else "Leave" endif

[DataNath · Accepted solution]

Hey @knobsdog, afaik MONTHSTART isn't a function in Alteryx and you'd want to use the DateTimeTrim() function instead, referencing the 'firstofmonth' date part. More on date functions here: https://help.alteryx.com/20221/designer/datetime-functions

[knobsdog]

So like this:    

IF DateTimeFormat(DateTimeToday(),"%d") > "15" and [scheduled_date]>= DateTimeTrim(DateTimeToday(),"%d") and [scheduled_date]<=(DateTimeAdd(DateTimeTrim(DateTimeToday(),"%d")),+15,"days"))then "Keep"

elseif DateTimeFormat(DateTimeToday(),"%d") < "15" and [scheduled_date]>=

DateTimeFormat(DateTimeAdd(DateTimeTrim(DateTimeToday(),"%d")(DateTimeAdd(DateTimeTrim(DateTimeToday(),"%d")),-1,"days")),+14,"days"),"%Y-%m-%d") and

[scheduled_date]<= DateTimeFormat( DateTimeAdd(DateTimeTrim(DateTimeToday(),"%d")),-1,"days"),"%Y-%m-%d") then "Keep"

else "Leave" endif

?

[DataNath · Accepted solution]

@knobsdog I’m not around my laptop to format your whole formula but one example of it would be DateTimeTrim(DateTimeToday(),"firstofmonth”)

From the documentation I linked above, here the dt (date) is DateTimeToday() and the f (date part) is ‘firstofmonth’.

So every instance you want to use month start (in this case first of month DateTimeTrim), you just need to wrap whatever date you’re referencing in the same way.

[knobsdog]

I misunderstood.  I'll rework with that framework.  Thank you again.