Getting Last Day of Previous Month in Netezza

Question

I want to get the last date of the previous month.

Something like this:

FunctionName(10.02.2011) Result should be 31.01.2011

In oracle we can do it using the last_day() function as suggested in this question question. Is there a similar function to do the same in Netezza Database.

Netezza is based on Postgres, so this might help you . . . http://stackoverflow.com/questions/8944721/how-do-i-determine-the-last-day-of-the-previous-month-using-postgresql. — Gordon Linoff, Sep 28 '12 at 18:12

score 4 · Answer 1 · edited Nov 20 '12 at 19:15

4

last_day() exists in Netezza (not sure which version we're on)

select extract(day from last_day(to_date('20121001','YYYYMMDD') - interval '1 month' ));
 DATE_PART 
-----------
        30

edited Nov 20 '12 at 19:15

Mike

47,263
29
113
177

answered Nov 20 '12 at 18:53

Kevin Postlewaite

595
4
14

score 1 · Answer 2 · answered Oct 29 '13 at 03:52

1

For Netezza, it would use add_month() and last_day() functions to get the result. Take '2013-01-01' for example.

select last_day(add_month('2013-01-01',-1));

Result: 2013-12-31

answered Oct 29 '13 at 03:52

Alan

37
7

The function is actually `add_months`. Otherwise your answer is working perfectly. – Pero Jun 12 '23 at 11:41

score 0 · Answer 3 · answered Sep 29 '12 at 14:15

I don't have Netezza available, but the following might work:

select <date> - datepart('day', <date>) - 1

This subtracts the current day of the month from the date, and then one more day to go back to the last day of the previous month.

You can do this also with:

select date(current_Date) - datepart('day', current_Date) - 1

for the current date.

score 0 · Answer 4 · answered Oct 05 '12 at 17:32

Similar to Gordon's answer, I've tested this on NZ 6 and it works to get the last day of the previous month by subtracting the current day # from the current date. You can replace current_timestamp with your date field.

select current_timestamp, current_timestamp - extract(day from current_timestamp)

Results:

2012-10-05 13:35:05 2012-09-30 00:00:00

score 0 · Answer 5 · answered Dec 06 '12 at 12:12

0

select extract(day from (select (last_day(to_char(current_timestamp,'dd/mm/yyyy')))))

answered Dec 06 '12 at 12:12

Mariappan Subramanian

9,527
8
32
33

1

A little context would make this a better answer. – Abizern Dec 06 '12 at 12:31

Getting Last Day of Previous Month in Netezza

5 Answers5