10

I need a function in Oracle like this.

When i giving a parameter a simple date. Then function should getting me last day of the previous month.

Example: 

FunctionName(10.02.2011) Result should be 31.01.2011

FunctionName(21.03.2011) Result should be 28.02.2011

FunctionName(31.07.2011) Result should be 30.06.2011 (Even date is last day of month)

How can i do that? By the way, i never use Oracle .

demongolem
  • 9,474
  • 36
  • 90
  • 105
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364

4 Answers4

37
SELECT LAST_DAY(ADD_MONTHS(yourdate,-1))
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Lamak
  • 69,480
  • 12
  • 108
  • 116
9

As an alternative to the other answers here, you can also find the last day of the previous month by getting the day before the first day of this month

SELECT trunc(your_date, 'MM')-1 as new_date from your_table

I would probably still recommend using last_day(add_months(xxx,-1)) but just showing an alternative.

Mike Meyers
  • 2,885
  • 1
  • 20
  • 26
4

select LAST_DAY(ADD_MONTHS(sysdate, -1)) from dual;

format resulting date as you like (I'll leave that one for you ;)

tbone
  • 15,107
  • 3
  • 33
  • 40
0

With this you can also get the last BUSINESS DAY of the previous month, or any other month changing the -1.

SELECT (trunc(LAST_DAY(ADD_MONTHS(sysdate,-1)), 'iw') + 4) from dual
LEBRO
  • 1