create or replace function uf_gettime(
---获取时间
ls_time in date,ls_span in number)
return date
as
ln_endtime date;---0天,1旬 ,2 月,3 年begin
if ls_span=0 then ---日,获取上一天的8点
ln_endtime:=to_date(substr(to_char(ls_time),1,10)||'08:00:00')-1;
end if;
if ls_span=1 then ----旬获取上一旬的第一天8点
select case when to_number(to_char(ls_time,'dd'))<11 then to_date(substr(to_char(add_months(ls_time,-1)),1,8)||'2108:00:00') when to_number(to_char(ls_time,'dd'))<21 then to_date(substr(to_char(ls_time),1,8)||'0108:00:00') else to_date(substr(to_char(ls_time),1,8)||'1108:00:00') end case into ln_endtime from dual;
end if;
if ls_span=2 then ----月获取上一月的1日的8点
select to_date(substr(to_char(add_months(ls_time,-2)),1,8)||'0108:00:00') into ln_endtime from dual;
end if;
if ls_span=3 then ----年获取上一年的1月1日的8点
select to_date(substr(to_char(add_months(ls_time,-12)),1,4)||'-01-0108:00:00') into ln_endtime from dual;
end if;
return ln_endtime;
end ;
上面函数编译没问题,select uf_gettime(sysdate,2) from dual;也没问题,但调试通过不过,异常
ora-01861 文字与格式字符串不匹配。
请指教,谢谢!!
---获取时间
ls_time in date,ls_span in number)
return date
as
ln_endtime date;---0天,1旬 ,2 月,3 年begin
if ls_span=0 then ---日,获取上一天的8点
ln_endtime:=to_date(substr(to_char(ls_time),1,10)||'08:00:00')-1;
end if;
if ls_span=1 then ----旬获取上一旬的第一天8点
select case when to_number(to_char(ls_time,'dd'))<11 then to_date(substr(to_char(add_months(ls_time,-1)),1,8)||'2108:00:00') when to_number(to_char(ls_time,'dd'))<21 then to_date(substr(to_char(ls_time),1,8)||'0108:00:00') else to_date(substr(to_char(ls_time),1,8)||'1108:00:00') end case into ln_endtime from dual;
end if;
if ls_span=2 then ----月获取上一月的1日的8点
select to_date(substr(to_char(add_months(ls_time,-2)),1,8)||'0108:00:00') into ln_endtime from dual;
end if;
if ls_span=3 then ----年获取上一年的1月1日的8点
select to_date(substr(to_char(add_months(ls_time,-12)),1,4)||'-01-0108:00:00') into ln_endtime from dual;
end if;
return ln_endtime;
end ;
上面函数编译没问题,select uf_gettime(sysdate,2) from dual;也没问题,但调试通过不过,异常
ora-01861 文字与格式字符串不匹配。
请指教,谢谢!!
select to_date(substr(to_char(add_months(ls_time,-2)),1,8)||'0108:00:00') into ln_endtime from dual;
end if;
换成如下:
if ls_span=2 then ----月获取上一月的1日的8点
select to_date(substr(to_char(add_months(ls_time,-1),'yyyy-mm-dd'),1,8)||'0108:00:00','yyyy-mm-dd hh24:mi:ss') into ln_endtime from dual;
end if;
......
if ls_span=1 then ----旬获取上一旬的第一天8点
select case when to_number(to_char(ls_time,'dd'))<11 then to_date(substr(to_char(add_months(ls_time,-1)),1,8)||'2108:00:00') when to_number(to_char(ls_time,'dd'))<21 then to_date(substr(to_char(ls_time),1,8)||'0108:00:00') else to_date(substr(to_char(ls_time),1,8)||'1108:00:00') end case into ln_endtime from dual;
end if;
......改成如下:
......
if ls_span=1 then ----旬获取上一旬的第一天8点
select case when to_number(to_char(ls_time,'dd'))<11 then to_date(substr(to_char(add_months(ls_time,-1)),1,8)||'2108:00:00') when to_number(to_char(ls_time,'dd'))<21 then to_date(substr(to_char(ls_time),1,8)||'0108:00:00') else to_date(substr(to_char(ls_time),1,8)||'1108:00:00') end into ln_endtime from dual;
end if;
......