Solved

date function using last_day in oracle

Posted on 2013-12-04
6
558 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 74

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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 115
oracle 11g 23 106
oracle date format checking 7 26
Oracle TRC and TRM files are taking too much space at Oracle client computer. 5 23
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

790 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