create or replace function found_date return date is d date; i varchar2(2); begin select to_char(to_date('20090501','yyyymmdd'),'d') into i from dual if i='2' then return to_date('20090501','yyyymmdd'); else select next_day(to_date('20090501','yyyymmdd'),2) into d from dual; return d; end; /
create or replace procedure first_mon reutrn date is v_date begin SELECT next_day( to_date(to_char(SYSDATE,''yyyy-mm'')||''-01'',''yyyy-mm-dd''), ''星期一'' ) into v_date from dual; return v_date end;
SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;NEXT_DAY( ——— 03-JUL-06
顶,不过在我机器上报1846错误,需要将monday改为中文'星期一'。 不过稍微改一下,用下面的,就可以和区域没有关系: select next_day(trunc(sysdate, 'MONTH'),2) from dual;
忘了把-1了:select next_day(trunc(sysdate, 'MONTH')-1,2) from dual;
create or replace function f_firstweek(f_day date) return date is v_f_firstmon date; begin select next_day(add_months(last_day(trunc(f_day))+1,-1),'星期一') into v_f_firstmon from dual; return v_f_firstmon; exception when others then v_f_firstmon:=null; return v_f_firstmon; end;
is
d date;
i varchar2(2);
begin
select to_char(to_date('20090501','yyyymmdd'),'d') into i from dual
if i='2' then
return to_date('20090501','yyyymmdd');
else
select next_day(to_date('20090501','yyyymmdd'),2) into d from dual;
return d;
end;
/
is
v_date
begin
SELECT next_day( to_date(to_char(SYSDATE,''yyyy-mm'')||''-01'',''yyyy-mm-dd''), ''星期一'' )
into v_date
from dual;
return v_date
end;
———
03-JUL-06
顶,不过在我机器上报1846错误,需要将monday改为中文'星期一'。 不过稍微改一下,用下面的,就可以和区域没有关系:
select next_day(trunc(sysdate, 'MONTH'),2) from dual;
v_f_firstmon date;
begin
select next_day(add_months(last_day(trunc(f_day))+1,-1),'星期一') into v_f_firstmon from dual;
return v_f_firstmon;
exception
when others then
v_f_firstmon:=null;
return v_f_firstmon;
end;