?
Solved

date function using last_day in oracle

Posted on 2013-12-04
6
Medium Priority
?
563 Views
Last Modified: 2013-12-04
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
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 39696358
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
 
LVL 13

Expert Comment

by:Ashok
ID: 39696377
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
 
LVL 13

Expert Comment

by:Ashok
ID: 39696401
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Closing Comment

by:anumoses
ID: 39696415
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39696477
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
 
LVL 13

Expert Comment

by:Ashok
ID: 39696501
sdstuber,

Thanks for your excellent input.  I agree with you.

Ashok
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question