Improve company productivity with a Business Account.Sign Up

x
?
Solved

oracle date comparision

Posted on 2010-11-29
3
Medium Priority
?
406 Views
Last Modified: 2012-05-10
experts... how do i compare two dates in oracle. I have 2 variables
first(v_date1) of varchar2(30) where I get a date in the format 'yyyy-mm-dd hh24:mi' and have another variable(v_date2) of varchar2(30) where i get a value from a table of 'date' column type by doing
select to_char(i_date )
into v_date2
from table1
where col1 = some_value

I want to see if v_date2 is less than v_date1.  How do i do this????
0
Comment
Question by:chickanna
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34233294
easiest  (and most accurate) way is to NOT convert your dates to strings.

declare v_date2 as a DATE,  not a varchar2.
and just select i_date not to_char(i_date)

since your v_date1 is already a string though,  conver it to a date and compare it

if to_date(v_date1,'yyyy-mm-dd hh24:mi')  < v_date2 then
.....
end if;


0
 
LVL 20

Expert Comment

by:gatorvip
ID: 34233300
convert to date, then compare them directly:


 to_date(v_date1, 'yyyy-mm-dd hh24:mi') > to_date( v_date2, 'yyyy-mm-dd hh24:mi' )
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34233318
if you must have both variables as string type  then force the v_date2 to have a consistent format

to_char(i_date,'yyyy-mm-dd hh24:mi')

that format should allow for direct string comparison.

or you could convert both back to dates with

to_date(v_date1,'yyyy-mm-dd hh24:mi')

to_date(v_date2,'yyyy-mm-dd hh24:mi')
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

595 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