select to_date('2012112', 'yyyymmdd') from dual ---- 肯定是 2012/11/2
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;
谢谢楼上的朋友们,针对你们给的写法,我自己也写了个。declare dateStr varchar2(20); begin dateStr := '201219'; dbms_output.put_line( substr(dateStr,0,4) || case length(substr(dateStr,5)) when 3 then '0' || substr(dateStr,5) when 2 then '0' || substr(dateStr,5,1) || '0' || substr(dateStr,6,1) else substr(dateStr,5) end ); end;
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
dateStr varchar2(20);
begin
dateStr := '201219';
dbms_output.put_line( substr(dateStr,0,4) ||
case length(substr(dateStr,5))
when 3 then '0' || substr(dateStr,5)
when 2 then '0' || substr(dateStr,5,1) || '0' || substr(dateStr,6,1)
else substr(dateStr,5) end );
end;