Search code examples
sqloracleisqlquery

Oracle SQL compare strings containing numbers, starting with 0 (zero)

I have a Date column, which is actually not in a date format. The date looks like: 25/10/2012. Now I have to compare 2 dates, but I cant find a pattern to do this, as the format is wrong.

What I have tried is: 

 SELECT * 
   from PARAMETER
  where NAME like 'Date_To' and SUBSTR(VALUE, 1, 2)<'25' 
    and SUBSTR(VALUE, 1, 2)>'05'

The problem I am facing is that this part: SUBSTR(PA_VALUE, 1, 2)>'05' is not working as there is a 0(zero) infront of the number. Are there any solutions?

Solution

  • try like 

    SELECT * 
from PARAMETER
where  NAME like 'Date_To' 
 and cast ( SUBSTR(VALUE, 1, 2) as int ) <cast ('25' as int)
 and cast ( SUBSTR(VALUE, 1, 2) as int) > cast ('05' as int )