最初由 宏悦 发布
[B]lz算法有问题,也太繁琐了。select next_day(sysdate-7,'星期六') from dual;多简单 [/B]
呵呵,你的方法跟数据库设置相关的.如果date_language不是简体中文,就完蛋了.
[php]
SQL> select next_day(sysdate-7,'星期六') from dual;
NEXT_DAY(SYSDATE-7,'星期六')
----------------------------
2007-8-11 14:13:32
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
SQL> alter session set NLS_DATE_LANGUAGE='AMERICAN';
Session altered
SQL> select next_day(sysdate-7,'星期六') from dual;
select next_day(sysdate-7,'星期六') from dual
ORA-01846: 周中的日无效
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE AMERICAN
而用next_day(sysdate-7,7),即用数字表示星期的方法则可以避免这个问题
SQL> select trunc(next_day(sysdate-7,7)) from dual;
TRUNC(NEXT_DAY(SYSDATE-7,7))
----------------------------
2007-8-11
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE AMERICAN
SQL> alter session set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE';
Session altered
SQL> select next_day(sysdate-7,7) from dual;
NEXT_DAY(SYSDATE-7,7)
---------------------
2007-8-11 14:22:50
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
不过在用数字作为日期的时候,next_day函数不能够直接赋值给变量,只能够通过select into方式.
SQL> create or replace function Fn_GetFirstMonDay(vDay Date) return date is
2 Result date;
3 begin
4 Result :=next_day(trunc(vDay),2);
5 return(Result);
6 end Fn_GetFirstMonDay;
7 /
Function created
SQL> select Fn_GetFirstMonDay(sysdate) from dual;
select Fn_GetFirstMonDay(sysdate) from dual
ORA-01846: 周中的日无效
ORA-06512: 在"LIS.FN_GETFIRSTMONDAY", line 4
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
SQL> alter session set NLS_DATE_LANGUAGE='AMERICAN';
Session altered
SQL> select Fn_GetFirstMonDay(sysdate) from dual;
select Fn_GetFirstMonDay(sysdate) from dual
ORA-01846: 周中的日无效
ORA-06512: 在"LIS.FN_GETFIRSTMONDAY", line 4
--此时无论NLS_DATE_LANGUAGE都出相同的错误
SQL>
SQL> create or replace function Fn_GetFirstMonDay(vDay Date) return date is
2 Result date;
3 begin
4 /*Result :=next_day(trunc(vDay),2);*/
5 Select next_day(trunc(vDay),2) into Result from dual;
6 return(Result);
7 end Fn_GetFirstMonDay;
8 /
Function created
SQL> select Fn_GetFirstMonDay(sysdate) from dual;
FN_GETFIRSTMONDAY(SYSDATE)
--------------------------
2007-8-20
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE AMERICAN
SQL> alter session set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE';
Session altered
SQL> select Fn_GetFirstMonDay(sysdate) from dual;
FN_GETFIRSTMONDAY(SYSDATE)
--------------------------
2007-8-20
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
--而用星期几的就可以直接赋值
SQL> create or replace function Fn_GetFirstMonDay(vDay Date) return date is
2 Result date;
3 begin
4 Result :=next_day(trunc(vDay),'星期一');
5 /*Select next_day(trunc(vDay),2) into Result from dual;*/
6 return(Result);
7 end Fn_GetFirstMonDay;
8 /
Function created
SQL> select Fn_GetFirstMonDay(sysdate) from dual;
FN_GETFIRSTMONDAY(SYSDATE)
--------------------------
2007-8-20
SQL>
[/php]