with t1 as ( select '2012-05-31 23:11:12' c1 from dual union all select '' c1 from dual union all select '2012-05-22 22:22:22' c1 from dual ) select nvl(to_char(to_date(c1,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss'),0) c1 from t1 c1 ------------------------ 1 20120531231112 2 0 3 20120522222222
with t1 as
(
select '2012-05-31 23:11:12' c1 from dual
union all
select '' c1 from dual
union all
select '2012-05-22 22:22:22' c1 from dual
)
select nvl(to_char(to_date(c1,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss'),0) c1
from t1
c1
------------------------
1 20120531231112
2 0
3 20120522222222