Rolling 6 month in Netezza

Question

I need to delete data older than 180 days from my agg table

Below delete command doesn't seems to be working

Delete from agg_table where sale_date <= '20231101' - 180

Delete from agg_table where sale_date <= '20231101' - interval'180 days'

I have tried few other options as well but it doesn't seem to work.

Sale_date in of date datatype

Answer 1

The issue with your DELETE command is that you are not correctly calculating the date that is 180 days before '20231101'.

you can use DATE_SUB function, here is how query will look like it

DELETE FROM agg_table 
WHERE sale_date <= DATE_SUB('2023-11-01', INTERVAL 180 DAY);

oh there is one more function which we can use as well which is DATE_ADD(Subtracting negative days)

DELETE FROM agg_table 
WHERE sale_date <= DATE_ADD('2023-11-01', INTERVAL -180 DAY);

Note: make sure to update the date format as per your database