Solved

Date function

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

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

863 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

23 Experts available now in Live!

Get 1:1 Help Now