Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date function

Posted on 2013-12-04
3
Medium Priority
?
514 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 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

971 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