oracle時間函數

一、Oracle的日期函數:

Oracle從8i開始就提供了大量的日期函數，這些日期函數包括對日期進行加減、轉換、截取等功能。下面是Oracle提供的日期函數一覽表 FunctionUseADD_MONTHSAdds months to a dateLAST_DAYComputes the last day of the monthMONTHS_BETWEENDetermines the number of months between two datesNEW_TIMETranslates a time to a new time zoneNEXT_DAYReturns the date of the next specified weekday ROUNDRounds a date/time value to a specified elementSYSDATEReturns the current date and timeTO_CHARConverts dates to stringsTO_DATEConverts strings and numbers to datesTRUNCTruncates a date/time value to a specific element

二、日期加減：

在Oralce中，對日期進行加減操作的默認單位是天，也就是說如果我們向當前日期加1的話是加上一天，而不是一秒或一小時。那麼對一天中的一段時間進行加減要怎麼做呢？很簡單！只需將它們轉化為以天為單位即可。

【1】為當前時間加上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將分鐘轉換成天。另外一個要注意的地方是：SQL*PLUS環境下默認的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含時、分、秒，所以我們這裡必須採用to_char的方式指定輸入的日期格式。

除此之外也可以通過在SQL*PLUS中執行下列語句修改默認的日期輸出格式，這樣的話就不需要通過to_char來轉換了，直接輸出就行。 alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";【2】為當前時間減去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隻需要加上一個負數即可以了。

三、月份加減：

月份的加減和日期加減相比要難了很多，因為每個月份的天數並不是固定的，可能是31,30,29,28。如果採用上面的方法將月份轉換成實際天數將不可避免地出現多個判斷，幸虧Oracle為我們提供了一個add_months函數，這個函數會自動判斷月份的天數。看看下面的例子：

【1】為當前時間加上6個月： SQL> select add_months(sysdate, 6) from dual;

ADD_MONTHS----------31-12月-08【2】為當前時間減去6個月： SQL> select add_months(sysdate, -6) from dual;

ADD_MONTHS----------31-12月-07【3】求兩個日期相差的月數：

通常情況下兩個時間相減將得到以天數為單位的結果，可是有時我們更希望得到以月為單位的結果，如果手動轉換這太麻煩了，所以Oracle又提供了一個函數，這個函數就是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.94928203months_between函數有2個參數，第一個參數是結束日期，第二個參數是開始日期，Oracle用第一個參數減去第二個參數得到月份數。所以結果有可能會是負數的。

四、年份加減：

Oracle並不直接提供對年份進行加減的函數，不過有了add_months和months_between函數，我們照樣可以做到。

【1】為當前日期加上2年： SQL> select add_months(sysdate, 2*12) two_years_later 2 from dual;

TWO_YEARS_----------30-6月 -10【2】求兩個日期相差幾年： SQL> select months_between(sysdate, 2 to_date("2006-06-30", "yyyy-mm-dd")) / 12 years_between 3 from dual;

YEARS_BETWEEN------------- 2直接將兩個日期相減，然後除以365天並不準確，但是不管一年有多少天它總是只有12個月，所以利用這一點我們可以先求出兩個日期相差的月數，再除以12就得出相差的年數了

五、求每月的最後一天：

SQL> select last_day(sysdate) last_day 2 from dual;

LAST_DAY----------31-8月 -08六、求每月的第一天：

Oracle提供了last_day讓我們能夠求出所在月份的最後一天，但沒有對應的first_day函數，如果有這方面的需求，只需要稍微動一下腦筋，利用last_day函數即可。例如下面的SQL語句就是求出下個月的第一天： SQL> select last_day(sysdate)+1 fisrt_day from dual;求當前月的第一天：select last_day(add_months(sysdate,-1))+1 from dual在這裡我們將「每月的第一天」轉換成「上個月最後一天的下一天」，問題就解決了！

七、求下一個星期幾：

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

下面我們看看如何得到下個星期五的日期：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: 周中的日無效很奇怪！是不？明明語法沒有問題，但為什麼會說「周中的日無效」呢？這裡就不得不說到Oracle中的語言和時區的問題了。下面這張圖是使用TOAD截取出來的客戶端session的語言和時區信息：

圖一

從圖中我們知道了客戶端的語言是簡體中文，日期使用的語言也是簡體中文，這就是為什麼上面的SQL語句出錯的原因了，因為在中文中只有「星期一，星期二」這樣的工作日表示，而沒有「Monday，Firday」這樣的寫法！

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

下周五----------04-7月 -08如果你不確定自己的時區或者你擔心從一個時區移植到另一個時區時，SQL語句會出錯，Oracle還允許你用數字的形式來表示工作日。但是要記得一點：1表示的是周日，2表示的是周一，3表示的是周二，依此類推。

例如我要查下個周三是什麼時候，則函數是這樣寫的：next_day(sysdate, 4)。 SQL> select next_day(sysdate,4) from dual;

NEXT_DAY(S----------02-7月 -08