Solved

Date function

Posted on 2013-12-04
3
510 Views
Last Modified: 2013-12-04
Showing an error message for user when end_date is less than start_date

I am using the format MM/RRRR as start_date  
  and MM/RRRR as end_date.

Using the below I get the end_date.

select to_char(last_day(to_date('10/2013','mm/yyyy')),'dd-mon-yyyy') from dual

If the user enters 10/2013 for start_date and 09/2013 for end_date, I need to tell its an error-  end date has to be greater than start date.

How can I have the code. I tired using

 TO_CHAR(:end_date,'DD-MON-YYYY') < TO_CHAR(:start_date,'DD-MON-YYYY')

but it does not work. Need to convert to date.
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
  • 2
3 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39696492
select last_day(to_date('10/2013','mm/yyyy')) from dual

returns a date

don't use to_char if you want don't want text.

note - DATE type values do NOT have a format.  So, if you are expecting your dates to look a certain way, that's not going to happen.
You need to convert your dates to text using TO_CHAR if you want dates to look a certain way because then they will be text which DOES have a format.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39696497
If you want your TEXT values to be dates and compared as DATEs then use TO_DATE


assuming your start and end are in DD-MON-YYYY format then use this...

TO_DATE(:end_date,'DD-MON-YYYY') < TO_DATE(:start_date,'DD-MON-YYYY')


or, if they are already dates then just compare them directly

:end_date < :start_date


if both of your values  are the MM/YYYY format above and you only want to compare the last day of month of each then try this


last_day(to_date(:end_date,'mm/yyyy'))  < last_day(to_date(:start_date,'mm/yyyy'))
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39696558
thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

635 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