oracle日期函式(5)

查出任一年月所含的工作日
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
/*------------------------------------------------------------------------------------------
函式名稱：Get_WorkingDays
中文名稱：求某一年月中共有多少工作日
作者姓名: XINGPING
編寫時間: 2004-05-22
輸入引數：NY:所求包含工作日數的年月，格式為yyyymm,如200405
返 回 值：整型值，包含的工作日數目。
演算法描述：
1).列舉出引數給出的年月中的每一天。這裡使用了一個表（ljrq是我的庫中的一張表。這個表可以是有權訪問的、記錄條數至少為31的任意一張表或檢視）來構造出某年月的每一天。
2).用這些日期和一個已知星期幾的日期相減(2001-12-30是星期天)，所得的差再對7求模。如果所求年月在2001-12-30以前，那麼所得的差既是負數，求模後所得值範圍為大於-6,小於0，如-1表示星期六,故先將求模的結果加7，再求7的模.
3).過濾掉結果集中值為0和6的元素，然後求count,所得即為工作日數目。
-------------------------------------------------------------------------------------------------*/
Result INTEGER;
BEGIN
SELECT COUNT(*) INTO Result
FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday
FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq
FROM (SELECT substr(100+ROWNUM,2,2) dd
FROM ljrq z WHERE Rownum<=31
) t
WHERE to_date(ny||t.dd,'yyyymmdd')
BETWEEN to_date(ny,'yyyymm')
AND last_day(to_date(ny,'yyyymm'))
)q
) a
WHERE a.weekday NOT IN(0,6);
RETURN Result;
END Get_WorkingDays;

______________________________________

還有一個版本
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
/*-----------------------------------------------------------------------------------------
函式名稱：Get_WorkingDays
中文名稱：求某一年月中共有多少工作日
作者姓名: XINGPING
編寫時間: 2004-05-23
輸入引數：NY:所求包含工作日數的年月，格式為yyyymm,如200405
返 回 值：整型值，包含的工作日數目。
算 法描述：使用Last_day函式計算出引數所給年月共包含多少天，根據這個值來構造一個迴圈。在這個迴圈中先求這個月的每一天與一個已知是星期天的日期 (2001-12-30是星期天)的差，所得的差再對7求模。如果所求日期在2001-12-30以前，那麼所得的差既是負數，求模後所得值範圍為大於 -6,小於0，如-1表示星期六,故先將求模的結果加7，再求7的模. 如過所得值不等於0和6（即不是星期六和星期天），則算一個工作日。
----------------------------------------------------------------------------------------*/
Result INTEGER := 0;
myts INTEGER; --所給年月的天數
scts INTEGER; --某天距2001-12-30所差的天數
rq DATE;
djt INTEGER := 1; --
BEGIN
myts := to_char(last_day(to_date(ny,'yyyymm')),'dd');
LOOP
rq := TO_date(ny||substr(100+djt,2),'yyyymmdd');
scts := rq - to_date('2001-12-30','yyyy-mm-dd');
IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN
Result := Result + 1;
END IF;
djt := djt + 1;
EXIT WHEN djt>myts;
END LOOP;
RETURN Result;
END Get_WorkingDays;

以上兩個版本的比較

第一個版本一條SQL語句就可以得出結果，不需要程式設計就可以達到目的。但需要使用任意一張有權訪問的、記錄條數至少為31的一張表或檢視。
第二個版本需要程式設計，但不需要表或者檢視。
這兩個版本都還存在需要完善的地方，即沒有考慮節日，如五一、十一、元旦、春節這些節假期都沒有去除。這些節假日應該維護成一張表，然後透過查表來去除這些節假日。

在oracle資料庫的開發中，常因為時間的問題大費周章，所以特地將ORACLE資料的日期函式收藏致此。乃供他日所查也。
add_months(d,n) 日期d加n個月
last_day(d) 包含d的月?的最後一天的日期
new_time(d,a,b) a?區的日期和??d在b?區的日期和??
next_day(d,day) 比日期d?，由day指定的周幾的日期
sysdate 當前的系?日期和??
greatest(d1,d2,...dn) ?出的日期列表中最後的日期
least(d1,k2,...dn) ?出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式??成字串
to_date(st [,fmt]) 字串st按fmt指定的格式?成日期?，若fmt忽略，st要用預設格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截斷到最近的日期
to_date 字串型別轉為換日期型別
字串中的相應位置上的字元,必須符合時間範圍的限制

查詢Oracle日期格式
----------------------------------

select * from nls_database_parameters;

得到結果如下表: 表中NLS_DATE_FORMAT表示日期格式.

PARAMETER	VALUE
-----------------------------------	-----------------------------------
NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CURRENCY	$
NLS_ISO_CURRENCY	AMERICA
NLS_NUMERIC_CHARACTERS	.,
NLS_CHARACTERSET	ZHS16GBK
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY	$
NLS_COMP	BINARY
NLS_NCHAR_CHARACTERSET	ZHS16GBK
NLS_RDBMS_VERSION	8.1.7.0.0

或者查詢V$NLS_PARAMETERS表,
select * from V$NLS_PARAMETERS;
也有類似結果

SQL>select to_date('2004-11-12 12-07-32','yyyy-mm-dd hh24-mi-ss') value from dual;
VALUE
-------------------
2004.11.12 12:07:32

SQL>select to_date('20041015') value from dual;
VALUE
-------------------
2004.10.15 00:00:00

SQL>select to_date('20041315') value from dual;
ERROR 位於第 1 行:
ORA-01861: 文字與格式字串不匹配

sysdate 當前日期和時間

SQL>select sysdate value from dual;
VALUE
-------------------
2003.11.23 17:09:01

last_day 本月最後一天

SQL>select last_day(sysdate) value from dual;
VALUE
-------------------
2003.11.30 17:08:17

add_months(d,n) 日期d後推n個月

SQL>select add_months(sysdate,2) value from dual;
VALUE
-------------------
2005.01.23 17:10:21

next_day(d,day) 日期d之後的第一週中,指定的那天(指定星期的第幾天)是什麼日期

SQL>select next_day(sysdate,1) value from dual;
VALUE
-------------------
2004.11.28 17:38:55

[@more@]