Automatically adjust (Teradata SQL) Where clause to previous month in YYYY-MM format

[fruehling]

So the first step on our flow is something we manually update monthly

This Teradata SQL query:

select DB.TABLE.field1,
DB.TABLE.Year_month
from DB.TABLE
where DB.TABLE.Year_month = '2023-01'

How do I adjust this WHERE clause to always be the previous month?

Any help is greatly appreciated

Edit:  As I'm still struggling with this, I have found that maybe I need something like this:

DateTimeFormat([DateTime_Out],"%Y-%m") 

or 

DateTimeAdd(DateTimeFirstOfMonth(), 1, "months")

But I can't exactly put together the syntax required

[Qiu]

@fruehling 
This is not exactly Alteryx question.

I found this and hope it helps.

https://www.encodedna.com/sqlserver/tips/how-to-get-previous-months-records-in-sql-server.htm

HAVING MONTH(SalesDate) < (SELECT DATEPART(M, DATEADD(M, 0, GETDATE())))

[fruehling]

Thank you!  You're right I guess I should have posted this on a sql board.  Really appreciate your reply!

[fruehling]

Edited main post

[DataNath]

Hey @fruehling, could you try something like this?

SELECT
DB.TABLE.field1,
DB.TABLE.Year_month
FROM DB.TABLE
WHERE PARSE("Year_month" AS DATE) = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)

[fruehling]

Thanks for that.  It did not like this.  It told me Year_month does not match a Defined Type name.  Is this because it is a Teradata source?

[DataNath · Accepted solution]

Ah my apologies @fruehling - I was testing this on Microsoft SQL, overlooked that it was Teradata! I've not worked with TD before but from a look over some documentation it should look something like this:

SELECT DB.TABLE.field1,
DB.TABLE.Year_month
FROM DB.TABLE
WHERE TO_DATE(DB.TABLE.Year_month, 'YYYY-MM') = ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1, -1)

[fruehling]

That seems to have worked as the test query button did not throw an error!  I need to make this change in several inputs, but I think you've got it.  I am guessing the -1 is going to previous month?

[DataNath]

Great to hear @fruehling! Yes the -1 is just part of the ADD_MONTHS function so it’s just adding -1 months to the first day of the current month to get the first day of the previous.

[fruehling]

DM sent.  Please let me know if you did not receive