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.
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
10/2013 isn't a date, it's text.  So, first convert the text into a date.

Then use last_day to get the last day of the month,  this result will also be a date.

If you want text in dd-mon-yyyy format,  then you must format your date into a character string with to_char



select to_char(last_day(to_date('10/2013','mm/yyyy')),'dd-mon-yyyy') from dual
0
 
AshokCommented:
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
0
 
AshokCommented:
select last_day(to_date('10/2013','mm/yyyy')) from dual;

will also return in
31-OCT-13
format.

OCT will all be uppercase.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anumosesAuthor Commented:
thanks
0
 
sdstuberCommented:
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
0
 
AshokCommented:
sdstuber,

Thanks for your excellent input.  I agree with you.

Ashok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.