CODEWITHSUNDEEP
sqlnetezza
TheGoat
TheGoat

Reputation: 2867

Rolling list of dates in Netezza

Similar to a question I asked here but related to Sybase SQL, I am looking to get a list of dates for my where clause to filter a table in Netezza.

Currently, my where clause needs to be updated each time I run the query with manual dates, I wish to automate this to use the last 36 months of data but from the end of the previous month. So if today is 10/10/2018 I wish to go back 36 months from 30/09/2018.

My current Netezza where clause looks as follows:

WHERE

    table.DateCol BETWEEN '2015-10-01' AND '2018-09-30'

I've found some examples here about extracting the last day of the month and first day but I can't figure out how to concatenate them together to get a list of dates using 'BETWEEN' and 'AND'

--last_day(now() - interval'36 month') + interval '1 day'  
--last_day(now() - interval'1 month')

Any pointers would be greatly appreciated.

Upvotes: 1

Views: 344

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Are you looking for this?

where t.datecol between last_day(current_date - interval '36 month') + interval '1 day' and
                        last_day(current_date - interval '1 month')

Upvotes: 1

Related Questions