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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

oracle date comparision

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????
  • 2
1 Solution
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;

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' )
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')

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now