日期:2014-05-17 浏览次数:20770 次
WITH t AS (SELECT '201101' num FROM dual UNION ALL SELECT '20111' num FROM dual UNION ALL SELECT '201111' num FROM dual) SELECT CASE WHEN length(num) = 6 THEN num WHEN length(num) = 5 THEN substr(num,1,4)||'0'||substr(num,5,1) ELSE '日期错误' END FROM t
------解决方案--------------------
针对第二个那种没有歧义的数据,这样应该可以
select to_date(decode(substr(datestr,5,1),'1',datestr,substr(datestr,1,4)||'0'||substr(datestr,5)),'yyyymmdd') from tablename;