Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date function

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 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