CODEWITHSUNDEEP
oracle-databaseplsql
Hoan Dang
Hoan Dang

Reputation: 2280

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

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 ?

Upvotes: 20

Views: 116825

Answers (7)

bilal Tashfeen
bilal Tashfeen

Reputation: 59

the simple way to do is : 

select first_name , last_day(hire_date) from employees;

i am using HR schema... you can get the last day of the mentioned month.

Upvotes: 0

Abid Mehdi
Abid Mehdi

Reputation: 1

Get First and Last Date of the Month Just change the Month digit to get the first and last date of the month

 select
  to_date('01/'|| '07/' || to_char(sysdate, 'YYYY'), 'dd/mm/yyyy') first,
  last_day(to_date('01/'|| '07/' || to_char(sysdate, 'YYYY'), 'dd/mm/yyyy')) last
  from  dual

Result:

first         last
-------------------------- 
01/02/2017  28/02/2017

Upvotes: 0

manjiri
manjiri

Reputation: 31

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;

Upvotes: 3

Jorgechu
Jorgechu

Reputation: 143

if you want know if your date is final mount

SELECT
case when
TO_DATE('19240430','YYYYMMDD') = LAST_DAY(TO_DATE('04/04/1924','MM/DD/YYYY'))
THEN 1 ELSE 0 END LAST_MOUNTH_DAY FROM DUAL

Upvotes: 0

Adel
Adel

Reputation: 1468

to get the selected month and year last day:

SELECT TO_CHAR(LAST_DAY(TO_DATE(:X_THE_MONTH ||'/01/' || :X_THE_YEAR,'MM/DD/YYYY')), 'dd') FROM DUAL

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300769

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().

Upvotes: 45

Civa
Civa

Reputation: 2176

This will show you the last day of month

SELECT  Max(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + level)
FROM    dual
CONNECT BY
    level <= LAST_DAY(ADD_MONTHS(SYSDATE, -1)) - LAST_DAY(ADD_MONTHS(SYSDATE, -2))

you can replace system date what ever date you want with to_date('04/04/1924','MM-DD-YYYY')

SELECT  Max(LAST_DAY(ADD_MONTHS(to_date('04/04/1924','MM-DD-YYYY'), -2)) + level)
FROM    dual
CONNECT BY
    level <= LAST_DAY(ADD_MONTHS(to_date('04/04/1924','MM-DD-YYYY'), -1)) - LAST_DAY(ADD_MONTHS(to_date('04/04/1924','MM-DD-YYYY'), -2))

or simply 

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

Upvotes: 0

Related Questions