Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get last day of a month from a given date?

Tags:

oracle

plsql

For example, the given date is 04/04/1924 and I want to find out the last day of February of the year 1924.

I came up with the add_month but it seems not flexible if I have different given month from data source

Any good ideas ?

like image 773
Hoan Dang Avatar asked Mar 16 '13 03:03

Hoan Dang


2 Answers

Oracle has a last_day() function:

SELECT LAST_DAY(to_date('04/04/1924','MM/DD/YYYY')) from dual;

SELECT LAST_DAY(ADD_MONTHS(to_date('04/04/1924','MM/DD/YYYY'), -1)) from dual;

SELECT LAST_DAY(ADD_MONTHS(to_date('04/04/1924','MM/DD/YYYY'), -2)) from dual;

Results:

April, 30 1924 00:00:00+0000

March, 31 1924 00:00:00+0000

February, 29 1924 00:00:00+0000

Use Add_Months() on your date to get the appropriate month, and then apply last_day().

like image 179
Mitch Wheat Avatar answered Oct 07 '22 01:10

Mitch Wheat


query inpl sql to get first day and last day of the month :

first day :

select to_date(to_char(LAST_DAY(sysdate),'YYYYMM'),'YYYYMM')  from dual;

Last day:

select LAST_DAY(to_date(to_char((sysdate),'YYYYMM'),'YYYYMM'))  from dual;
like image 32
manjiri Avatar answered Oct 07 '22 01:10

manjiri