0

(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.

fruehling
  • 1
  • 1

2 Answers2

0

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.

ElTête
  • 555
  • 1
  • 5
  • 15
  • Thank you for that. It didn't like it unfortunately. "Syntax Error: Data Type "Now" does not match a defined type name" – fruehling Feb 02 '23 at 16:02
  • @fruehling take a look at the doc [link](https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates/March-2019/Built-In-Functions/NOW/Example). You can try CURRANT_DATE() too instead of NOW(). did you write it in capital letters? May be it is case-sensitive. – ElTête Feb 03 '23 at 07:47
  • This question is tagged "teradata" not "sql-server". – Fred Feb 03 '23 at 18:13
0

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')

Andrew
  • 8,445
  • 3
  • 28
  • 46