Vertica IN-DB filter or SQL for all dates in the prior month

[cpowers]

Hello, 

I have a WF with a connection to a Vertica database with transactional information. Each time I run the WF (monthly on the 5th of the month) I'd like to filter the records that come out to be only ones from the prior month. So if I am running it the 5th of July I want any date with a June date (I'll need this to cross years too - so my Jan 5th 2023 run picks up all Dec 2022 dates). My date field is formatted YYYY-MM-DD HH:MM:SS and it is called createdtime. I've tried both to add to my initial in DB tool with some SQL and to come up with a statement to put into the IN-DB filter and have not had success yet. I appreciate the help, thanks!

[christine_assaad]

Hi @cpowers 

You can try below and replace "Date" with your variable:

"Date" >= DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) and "Date" <= DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Hope that helps. Cheers!

[cpowers]

Hi @christine_assaad - thanks for the response. If I put that into a IN-DB filter it says column M does not exist, if I add it to the IN-DB input it says column reference "createdtime" is ambiguous" which I've gotten that error before with other SQL I was trying. Not sure how to resolve. 

[cpowers · Accepted solution]

I got it to work in my In-DB SQL by adding - I am referencing more than one table in my select so had to specify the table in addition to the field in the statement

AND
MONTH(tablename.fieldname)=MONTH(ADD_MONTHS(NOW(),-1))
AND
YEAR(tablename.fieldname)=YEAR(ADD_MONTHS(NOW(),-1))