SQL & PL/SQL

Hi,

I have a column which stores date as VARCHAR2(23) in YYYY/MM/DD-HH:MM:SS:LLL format. I store another date as string in YYYY/MM/DD format in a string variable in my program. I need to compare these two dates with sql where clause.

Column in the table - TransferredDate - 2007/12/14-01:52:30:098
My string variable - sDate - 2008/08/10

I understand that storing date as VARCHAR2(23) is a bad design, but unfortunately I don't have control over the database.

For equality, the following works - where TransferredDate like sdate||'%'

But I don't get the correct results for "greater than" queries -

select Transferreddate from table where Transferreddate > '2008/08/10';

In the result I get all the dates greater than 2008/08/10 including 2008/08/10.

My problem is how do I cmpare two dates which are stored as strings using a sql where clause. Since I use proprietary programming language, I cannot make use of Oracle functions like to_date() etc. I can only use the where clause.

Any help would be appreciated.

Message was edited by:
user652075