• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

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.
0
anumoses
Asked:
anumoses
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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 recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now