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

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

But I can't exactly put together the syntax required

Date Time

Accepted answers

DataNath

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)

All comments

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335