Netezza - find the first date of the prior quarter-CodePudding

I'm trying to get the first day of the previous quarter from today's date however I can't find logic for Netezza SQL.

For SQL Server I could use the following:

select dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0)

There doesn't appear to be an equivalent of datediff in Netezza, any suggestions would be greatly appreciated

CodePudding user response：

=> select now(), (date_trunc('quarter', now()) - interval ('3 months'))::date as result;

         NOW         |   RESULT
--------------------- ------------
 2022-09-02 13:09:05 | 2022-04-01
(1 row)

CodePudding user response：

Based on a similar answer here I was able to adapt my code to the following:

WHERE TABLE_A.DATE_FIELD BETWEEN (SELECT TO_DATE(TO_CHAR(TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYYQ'),'YYYYQ')-1,'YYYYQ'),'YYYYQ')) AND (SELECT TO_DATE(TO_CHAR(CURRENT_DATE,'YYYYQ'),'YYYYQ'))