Solved

date function using last_day in oracle

Posted on 2013-12-04
6
549 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
  • 3
  • 2
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Closing Comment

by:anumoses
ID: 39696415
thanks
0
 
LVL 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now