Date function

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.
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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.
sdstuberCommented:
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'))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.