asked on

date function using last_day in oracle

The user will enter date parameters as start_date and end_date

format is MM/RRRR
eg start_date - 10/2013
end_date - 10/2013

I want the end_date to be the last day of the month. I tried using last_day function. But did not get the right format. If the user enters 10/2013 for end_date it should convert to 31-oct-2013.

Thanks in advance.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ashok

select LAST_DAY(TO_DATE(SUBSTR('10/2013', 1, INSTR('10/2013', '/')) || '1' || SUBSTR('10/2013', INSTR('10/2013', '/')), 'MM/DD/YYYY')) from Dual;

HTH
Ashok

Ashok

select last_day(to_date('10/2013','mm/yyyy')) from dual;

will also return in
31-OCT-13
format.

OCT will all be uppercase.

anumoses

ASKER

thanks

Sean Stuber

ashok111, both of your answers depend on implicit conversions

last_day(to_date('10/2013','mm/yyyy')) will not produce '31-oct-2013' reliably - it doesn't for me because my default format isn't dd-MON-yyyy

note the difference: MON vs mon - that's why you got "OCT" in your output rather than the requested "oct"

your first query, requires a lot of work to but is essentially the same as your second query and hence, has the same output problem.
using to_date and to_char with explicit masks both IN and OUT is the only way to get reliable results.

your second query is essentially just the original post but without the required formatting

Ashok

sdstuber,

Thanks for your excellent input. I agree with you.

Ashok