oracle 自定义函数问题

Question

一字段，类型为varchar2，里面存的是日期，但格式为yyyymmdd和yyyy-mm-dd，里面有部分数据为空,求写一自定义函数，能在查询数据时全部转换为yyyy-mm-dd格式，空的显示为空

Answer 1

1、不要使用函数，直接操作表就可以先把表中的这列全部转换成这样的日期类型 to_date(xxxx,'yyyy-mm-dd')
然后再转换成varchar2的日期类型to_char(xxxx,'yyyy-mm-dd')如tbl_test表中有这样的字段 start_time  select to_char(to_date(t.start_time,'yyyy-mm-dd'),'yyyy-mm-dd') as v_date
    from tbl_test t 
   where t.start_time is not null;2、如果硬要使用函数的话，也可以，只是效率慢点
 
  CREATE OR REPLACE FUNCTION func_get_char
(
    i_date    in    varchar2
)
    return    varchar2
as
    v_date     varchar2(30);
    v_return   varchar2(30);begin    v_date := i_date;    if v_date is null  then
       v_return :=null;
    else
       v_return := to_char(to_date(v_date,'yyyy-mm-dd'),'yyyy-mm-dd');
    end if;    return v_return;end func_get_char;
 

Answer 2

1楼：
我这样写了一个
create or replace function format_date(num in varchar) return varchar2
as
dt varchar2(20);
begin
      if num is null or num=0 or num=' ' then
         return null;
      elsif instr(num,'-')!=0 then return num;
      else
         select to_char(to_date(num,'yyyy-mm-dd'),'yyyy-mm-dd') into dt from dual;
         return dt;
      end if;
      exception when others then
          DBMS_OUTPUT.put_line ('数据错误');end;
没用elseif是正确的，我想如果格式正确就返回，但这样报错说函数没返回值，求解

Answer 3

elsif instr(num,'-')!=0 then return num;
  else
改写成
elsif instr(num,'-')!=0 then 
return num;

Answer 4

编译没问题，select format_date(2010-01-01) from dual运行不对

Answer 5

select decode(date1,null,null,to_date(date1,'yyyy-mm-dd')) from dual

Answer 6

楼主修改 num=0 修改为 num='0' ，因为num是字符串，不能和数字比较
  create or replace function format_date(num in varchar) return varchar2
as
dt varchar2(20);
begin
  if num is null or num='0' or num=' ' then
  return null;
  elsif instr(num,'-')!=0 then return num;
  else
  select to_char(to_date(num,'yyyy-mm-dd'),'yyyy-mm-dd') into dt from dual;
  return dt;
  end if;
 exception 
    when others then
    DBMS_OUTPUT.put_line ('数据错误');
    return substr( sqlerrm,1,100);
end;

Answer 7

select decode(trim(date1),null,null,to_date(date1,'yyyy-mm-dd')) from dual;

Answer 8

格式是yyyymmdd的日期字符串应该这样转换（假定字段名是dt）：
to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')
用下面的语句可以实现转换：
select case when length(dt)=8 then to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd') else dt end case from tablename;
或者用decode函数：
select decode(length(dt),null,null,8,to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd'),dt) from tablename;

Answer 9

可以直接sql试试
with temp as 
(
select '20111010' ds from dual
union 
select '2011-10-11' ds from dual
union 
select null ds from dual
)
select case when instr(ds, '-') <=0 then to_char(to_date(ds, 'yyyymmdd'), 'yyyy-mm-dd') else ds end rtn from temp

Answer 10

--试试Oracle的正则表达式,哈哈With xxx As
 (Select '20110506' cdate From dual Union All
  Select '2011-5-17' cdate From dual Union All
  Select '2011-05-28' cdate From dual Union All
  Select Null cdate From dual Union All
  Select '20110509' cdate From dual)
Select Case
         When t.cdate Is Null Then
          Null
         When regexp_like(t.cdate,
                          '^[[:digit:]]{1,4}-[[:digit:]]{1,2}-[[:digit:]]{1,2}$') Then
          cdate
         Else
          to_char(to_date(cdate, 'YYYYMMDD'), 'YYYY-MM-DD')
       End "有意思吧 哈哈!"
  From xxx t;