Visual Query Builder Prior Month

[cowannbell]

I'm building a workflow and using the Visual Query Builder.  I want to limit the records pulled directly from the pull and not after bringing it in.

I have a date field and I want to limit the records pulled to from the prior month.

How can I do that in the criteria field?

Thank you!

[BrandonB]

This depends on the database that you are puling from because the syntax will be specific to that source: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Visual-Query-Builder-datetime-Criteria... 

For example, the criteria may be > DateAdd(day, -60, GetDate()) as mentioned in the post

[cowannbell]

Well, I get that.  I can't use like 60 or 90 days etc.  I need it to look at the previous month.  I know the database is a IBM mainframe which I believe is Linux system.

[cowannbell]

It is an IBM db2 database.

[BrandonB · Accepted solution]

Probably best to do that in the WHERE clause of the query rather than trying to do it from the visual query builder. I'm not a DB2 expert but I believe it is something like

WHERE                                                 
   DATE_FIELD BETWEEN LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY
                  AND LAST_DAY(CURRENT_DATE - 1 MONTH)

and you swap out the DATE_FIELD with your date field

You can see the DB2 date functions here: https://www.db2tutorial.com/db2-date-functions/ 

[cowannbell]

Thank you so much.  That did the trick.  Thank you for the link also.  I will keep that handy.

Carol