Menu
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 .
819
Start with today's date using sysdate (3/27) and subtracts one month (2/27). We then Truncate the result using MM for the numerical value of the month(2). This will represent the first day of last month (2/1). The second manipulation requires the use of LAST_DAY instead of TRUNC.
ADD_MONTHS returns the date date plus integer months. A month is defined by the session parameter NLS_CALENDAR . The date argument can be a datetime value or any value that can be implicitly converted to DATE . The integer argument can be an integer or any value that can be implicitly converted to an integer.
select trunc(<date_input>,'MM') from dual; select trunc(<date_input>,'Mon') from dual; Cheers, Manik.
SELECT LAST_DAY(ADD_MONTHS(yourdate,-1))
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.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
