Automatically adjust (Teradata SQL) Where clause to previous month in YYYY-MM format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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())))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you! You're right I guess I should have posted this on a sql board. Really appreciate your reply!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Edited main post
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
DM sent. Please let me know if you did not receive
