how do I convert mmyy to last day of month in nete

Question

One of my column needs to be transformed into a date field. It contains a value that gives the YYMM and it should be translated into the last day of that month: For example, 1312 should become 12/31/2013.

I have tried various last_day, to_char functions but not able to convert 1312 in a date format. Please help !!

Answer 1

Netezza is based on Postgres, so maybe the Postgres method will work. Here is Postgres code that works (see here):

select to_date('1312'||'01', 'YYMMDD') + interval '1 month' - interval '1 day'

Answer 2

I would first convert the number to a date, then add 1 month and subtract 1 day.

select add_months(to_date(1312, 'yymm'), 1) - 1 as the_date