目錄
=========================================
1.Oracle的日期函數(shù)
2.日期加減
3.月份加減
4.年份加減
5.求每月的最后一天
6.求每月的第一天
7.求下一個(gè)星期幾

入門知識(shí)：

①Oracle中的日期時(shí)間存儲(chǔ)：
oracle數(shù)據(jù)庫中存放時(shí)間格式的數(shù)據(jù)，是以oracle特定的格式存貯的，占7個(gè)字節(jié)，與查詢時(shí)顯示的時(shí)間格式無關(guān)。不存貯秒以下的時(shí)間單位。
②Oracle中的日期時(shí)間顯示：
通常，客戶端與數(shù)據(jù)庫建立起連接后，oracle就會(huì)給一個(gè)缺省的時(shí)間格式數(shù)據(jù)的顯示形式，與所使用的字符集有關(guān)。一般顯示年月日，而不顯示時(shí)分秒。
③Oracle中的日期時(shí)間插入：
向表中插入數(shù)據(jù)時(shí)，如果不使用轉(zhuǎn)換函數(shù)，則時(shí)間字段的格式必須遵從會(huì)話環(huán)境的時(shí)間格式，否則不能插入。
④Oracle中的日期時(shí)間格式修改：
a.SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
b.冊(cè)表\hkey_local_machine\software\oracle\home0主鍵中增加一個(gè)字串(8i版本)，字串名為nls_date_format，字串的值為你希望定義的時(shí)間格式
前者只對(duì)當(dāng)前會(huì)話有效，也即是一旦你關(guān)閉了SQL*PLUS窗口或重新打開一個(gè)SQL*PLUS窗口，日期時(shí)間格式依然采用本地字符集對(duì)應(yīng)的日期時(shí)間格式。后者對(duì)所有客戶端應(yīng)用有效。當(dāng)兩者同時(shí)應(yīng)用時(shí)，以alter session的修改為準(zhǔn)。

一、Oracle的日期函數(shù):

Oracle從8i開始就提供了大量的日期函數(shù)，這些日期函數(shù)包括對(duì)日期進(jìn)行加減、轉(zhuǎn)換、截取等功能。下面是Oracle提供的日期函數(shù)一覽表

Function	Use
ADD_MONTHS	Adds months to a date
LAST_DAY	Computes the last day of the month
MONTHS_BETWEEN	Determines the number of months between two dates
NEW_TIME	Translates a time to a new time zone
NEXT_DAY	Returns the date of the next specified weekday
ROUND	Rounds a date/time value to a specified element
SYSDATE	Returns the current date and time
TO_CHAR	Converts dates to strings
TO_DATE	Converts strings and numbers to dates
TRUNC	Truncates a date/time value to a specific element

二、日期加減：

在Oralce中，對(duì)日期進(jìn)行加減操作的默認(rèn)單位是天，也就是說如果我們向當(dāng)前日期加1的話是加上一天，而不是一秒或一小時(shí)。那么對(duì)一天中的一段時(shí)間進(jìn)行加減要怎么做呢？很簡單！只需將它們轉(zhuǎn)化為以天為單位即可。

【1】為當(dāng)前時(shí)間加上30分鐘：

SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') now_date,

2 to_char(sysdate+(30/24/60), 'yyyy-mm-dd hh:mi:ss') new_date

3 from dual;

NOW_DATE NEW_DATE

-------------------------------------- --------------------------------------

2008-06-30 10:47:31 2008-06-30 11:17:31

SQL>

我們看到了在綠色高亮處使用30/24/60將分鐘轉(zhuǎn)換成天。另外一個(gè)要注意的地方是：SQL*PLUS環(huán)境下默認(rèn)的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含時(shí)、分、秒，所以我們這里必須采用to_char的方式指定輸入的日期格式。

除此之外也可以通過在SQL*PLUS中執(zhí)行下列語句修改默認(rèn)的日期輸出格式，這樣的話就不需要通過to_char來轉(zhuǎn)換了，直接輸出就行。

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

【2】為當(dāng)前時(shí)間減去30分鐘：

SQL> select to_char(sysdate+(-30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;

NEW_DATE

--------------------------------------

2008-06-30 10:24:59

只需要加上一個(gè)負(fù)數(shù)即可以了。

三、月份加減：

月份的加減和日期加減相比要難了很多，因?yàn)槊總€(gè)月份的天數(shù)并不是固定的，可能是31,30,29,28。如果采用上面的方法將月份轉(zhuǎn)換成實(shí)際天數(shù)將不可避免地出現(xiàn)多個(gè)判斷，幸虧Oracle為我們提供了一個(gè)add_months函數(shù)，這個(gè)函數(shù)會(huì)自動(dòng)判斷月份的天數(shù)。看看下面的例子：

【1】為當(dāng)前時(shí)間加上6個(gè)月：

SQL> select add_months(sysdate, 6) from dual;

ADD_MONTHS

----------

31-12月-08

【2】為當(dāng)前時(shí)間減去6個(gè)月：

SQL> select add_months(sysdate, -6) from dual;

ADD_MONTHS

----------

31-12月-07

【3】求兩個(gè)日期相差的月數(shù)：

通常情況下兩個(gè)時(shí)間相減將得到以天數(shù)為單位的結(jié)果，可是有時(shí)我們更希望得到以月為單位的結(jié)果，如果手動(dòng)轉(zhuǎn)換這太麻煩了，所以O(shè)racle又提供了一個(gè)函數(shù)，這個(gè)函數(shù)就是months_between。

SQL> select months_between(sysdate,

2 to_date('2008-01-01 01:00:00', 'yyyy-mm-dd hh:mi:ss')) result

3 from dual;

RESULT

----------

5.94928203

months_between函數(shù)有2個(gè)參數(shù)，第一個(gè)參數(shù)是結(jié)束日期，第二個(gè)參數(shù)是開始日期，Oracle用第一個(gè)參數(shù)減去第二個(gè)參數(shù)得到月份數(shù)。所以結(jié)果有可能會(huì)是負(fù)數(shù)的。

四、年份加減：

Oracle并不直接提供對(duì)年份進(jìn)行加減的函數(shù)，不過有了add_months和months_between函數(shù)，我們照樣可以做到。

【1】為當(dāng)前日期加上2年：

SQL> select add_months(sysdate, 2*12) two_years_later

2 from dual;

TWO_YEARS_

----------

30-6月 -10

【2】求兩個(gè)日期相差幾年：

SQL> select months_between(sysdate,

2 to_date('2006-06-30', 'yyyy-mm-dd')) / 12 years_between

3 from dual;

YEARS_BETWEEN

-------------

直接將兩個(gè)日期相減，然后除以365天并不準(zhǔn)確，但是不管一年有多少天它總是只有12個(gè)月，所以利用這一點(diǎn)我們可以先求出兩個(gè)日期相差的月數(shù)，再除以12就得出相差的年數(shù)了

五、求每月的最后一天：

SQL> select last_day(add_months(sysdate,2)) last_day

2 from dual;

LAST_DAY

----------

31-8月 -08

六、求每月的第一天：

Oracle提供了last_day讓我們能夠求出所在月份的最后一天，但沒有對(duì)應(yīng)的first_day函數(shù)，如果有這方面的需求，只需要稍微動(dòng)一下腦筋，利用last_day函數(shù)即可。例如下面的SQL語句就是求出下個(gè)月的第一天：

SQL> select last_day(sysdate)+1 fisrt_day

2 from dual;

FISRT_DAY

----------

01-7月 -08

在這里我們將“每月的第一天”轉(zhuǎn)換成“上個(gè)月最后一天的下一天”，問題就解決了！

七、求下一個(gè)星期幾：

有時(shí)候我們會(huì)碰上“下個(gè)星期五是幾號(hào)啊？”這樣常見的問題。Oracle為此提供了一個(gè)函數(shù)：next_day，它的語法是這樣的：next_day(date, string)。其中第一個(gè)參數(shù)date告訴Oracle從什么時(shí)候開始算起，第二個(gè)參數(shù)string則告訴Oracle要取的工作日。

下面我們看看如何得到下個(gè)星期五的日期：

SQL> select next_day(sysdate, 'Friday') "Next Friday" from dual;

select next_day(sysdate, 'Friday') "Next Friday" from dual

ERROR at line 1:

ORA-01846: 周中的日無效

很奇怪！是不？明明語法沒有問題，但為什么會(huì)說“周中的日無效”呢？這里就不得不說到Oracle中的語言和時(shí)區(qū)的問題了。下面這張圖是使用TOAD截取出來的客戶端session的語言和時(shí)區(qū)信息：

圖一

從圖中我們知道了客戶端的語言是簡體中文，日期使用的語言也是簡體中文，這就是為什么上面的SQL語句出錯(cuò)的原因了，因?yàn)樵谥形闹兄挥?#8220;星期一，星期二”這樣的工作日表示，而沒有“Monday，F(xiàn)irday”這樣的寫法！

SQL> select next_day(sysdate,'星期五') "下周五" from dual;

下周五

----------

04-7月 -08

如果你不確定自己的時(shí)區(qū)或者你擔(dān)心從一個(gè)時(shí)區(qū)移植到另一個(gè)時(shí)區(qū)時(shí)，SQL語句會(huì)出錯(cuò)，Oracle還允許你用數(shù)字的形式來表示工作日。但是要記得一點(diǎn)：1表示的是周日，2表示的是周一，3表示的是周二，依此類推。

例如我要查下個(gè)周三是什么時(shí)候，則函數(shù)是這樣寫的：next_day(sysdate, 4)。

SQL> select next_day(sysdate,4) from dual;

NEXT_DAY(S

----------

02-7月 -08

看一下日歷是不是正確的，確實(shí)不錯(cuò)！呵呵

-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。

posted on 2008-06-30 10:23 Paul Lin 閱讀(8750) 評(píng)論(11) 編輯收藏所屬分類: Oracle 開發(fā)

FeedBack: