SQL - Automatically adjust Where clause to previous month in YYYY-MM format-CodePudding

(This is all steps in containers within an Alteryx flow that is connecting to a Teradata source)

My SQL is incredibly rusty as it's been almost 8 years since I've needed to use it. I know this is a quite basic question. Basically I have several queries that need to be manually adjusted monthly to shift the month. in YYYY-MM format. They look like this:

Is the main one where I just adjust one backwards one month

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

This is the secondary one where I adjust one backwards one month, and the others are same month or plus a month or more.

and A.B_MONTH in ('2022-12-01', '2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01','2023-05-01') 
and B.Year_month = '2023-01'

How do I adjust the where clause to always be the needed relative references?

Any help is greatly appreciated

I tried using concat but it choked for some reason.

CodePudding user response：

You can try this:

select DB.TABLE.field1, DB.TABLE.Year_month
from DB.TABLE
where DB.TABLE.Year_month = DATE_FORMAT( NOW() - INTERVAL 1 MONTH, '%Y/%m')

I don't understand your second need, but you can do it similar to my response. Just play with the NOW() - INTERVAL X.

CodePudding user response：

Pretty basic stuff. ADD_MONTHS to move your month around, TO_CHAR for your desired format. To get the previous month:

select to_char(add_months(current_date,-1), 'YYYY-MM')