previous month daate

[Hi2023]

does anyone know how to pull the previous month date data for a report each time it is run?

I am using the SQL Editor in Alteryx

DateTimeFormat(CRG_STAT) >= DateTimeFormat(DateTimeAdd(DateTimeNow(), -1, "month"))

example I found BUT

This one has time inside of it -mine is NOT time just a date.

thanks

[CatheyH]

Have you tried DateTimeToday()? I set the field as "date" rather than "datetime"

[Luke_C · Accepted solution]

Hi @Hi2023 

If you're using SQL the Alteryx date syntax won't work. Example SQL syntax:

put your field instead of pdate

SELECT * FROM  tablename
WHERE pdate >= DATEADD(day,-30,GETDATE()) 
and   pdate <= getdate()

Or use DateDiff

SELECT * FROM  tablename 
WHERE DATEDIFF(day,pdate,GETDATE()) between 0 and 30 

[vpogaku]

Hi Luke,

I have a similar use case where I always want my date field to calculate till previous month end in my YTD calculations.

We want our YTD logic to work till end of previous month but should not include current month.

For E.g., Even though we don't have current month data (Jan 24) our YTD should work for one prior month of Dec 23 as we won't get the current month Jan 24 values until we reach the end of current month Jan 24.

Thanks, in advance.

[vpogaku]

Hi Hi2023,

I have a similar use case where I always want my date field to calculate till previous month end in my YTD calculations.

We want our YTD logic to work till end of previous month but should not include current month.

For E.g., Even though we don't have current month data (Jan 24) our YTD should work for one prior month of Dec 23 as we won't get the current month Jan 24 values until we reach the end of current month Jan 24.

Can you please share your thoughts here?