Search code examples
netezza

Last day of the next month in Netezza

I have a table that brings in monthly customer accounts on the last day of the month. If an account existed in the prior month, but not in the current month, I create add a 'D' for drop to the next month indicating a customer has dropped service. On the date this occurs at, I need it to reflect the last day of the next month.

I've read several examples on this site, but I haven't found one specifically for Netezza that deals with my case. I have tried something like LAST_DAY(My_TIMESTAMP + '1 mon'), but this doesn't work for every month. For example, Jan 31, 2022 cannot be Feb 31, 2022 as Feb only has 28 days. I need this to come back as Feb 28, 2022. Has anyone developed anything that can account for this?

Solution

  • last_day(add_months(...))

    The sql to use is

    select last_day(add_months(my_timestamp,1)) ...

    add_months will correctly adjust the date in cases like Jan-31 and Feb-28 etc.