Mani Mozhi Mani Mozhi - 2 months ago 6
SQL Question

How to retrieve varchar data in table ex: '2012/05/01 09:42'

I have few data in Intime table like

2012/05/01 09:41
2012/05/05 09:47
2012/05/07 08:30
2012/05/09 08:45
2012/05/10 09:41,


Query will be like

select Intime
from table
where Intime>2012/05/01 09:30


its fetching only the 2012/05/01 not 09:30 the datatype of Intime is varchar2

Answer

You need to convert your varchar2 column to date and then do your comparison. You need to use it this way:

SELECT TO_DATE (Intime, 'YYYY/MM/DD HH:MI') D_ATE
FROM TABLE    
WHERE to_date(Intime ,'YYYY/MM/DD HH:MI') > to_date('2012/05/01 09:30','YYYY/MM/DD HH:MI');

Demo:

 with dt_tbl (Intime) as ( select '2012/05/01 09:30' from dual
                         UNION ALL
                         select '2012/05/01 09:42' from dual
                         UNION ALL
                         select '2012/05/01 09:29' from dual
                         UNION ALL
                         select '2012/05/01 09:35' from dual)
SELECT TO_DATE (Intime, 'YYYY/MM/DD HH:MI') D_ATE
FROM dt_tbl    
WHERE to_date(Intime ,'YYYY/MM/DD HH:MI') > to_date('2012/05/01 09:30','YYYY/MM/DD HH:MI');
Comments