ORACLE 常用函數——日期/時間函數

藍調書軒 2016-08-13

展開全文

---------------------------------------------日期/時間函數-----------------------------------------------
--1：  SYSDATE
用來得到系統的當前日期

SELECT SYSDATE FROM DUAL;

--2：  ADD_MONTHS
增加或減去月份

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL;

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

--3:   LAST_DAY
返回日期的最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

--4:   MONTHS_BETWEEN(date2,date1)
給出date2-date1的月份

--參數的格式需要注意

SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03', 'YYYY-MM-DD'), TO_DATE('2011-01-23', 'YYYY-MM-DD')) FROM DUAL;

SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;

--SELECT MONTHS_BETWEEN('2011-1月-23', '2011-9月-1') FROM DUAL;  文字與格式字符串不匹配

--5：    NEW_TIME(date,'this','that')
給出在this時區=other時區的日期和時間

SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,
       TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;


簡寫                  時區
AST OR ADT          大西洋標準時間
HST OR HDT          阿拉斯加—夏威夷時間
BST OR BDT          英國夏令時
MST OR MDT          美國山區時間
CST OR CDT          美國中央時區
NST                 新大陸標準時間
EST OR EDT          美國東部時間
PST OR PDT          太平洋標準時間
GMT                 格倫威治標準時間
YST OR YDT          Yukon標準時間

--6：    NEXT_DAY
返回與制定日期在同一個星期或之后一個星期內的，你所要求的星期天數的確切日期。

--星期日 = 1  星期一 = 2  星期二 = 3  星期三 = 4  星期四 = 5  星期五 = 6  星期六 = 7

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;

--7:    CURRENT_DATE
當前會話時區中的當前日期

SELECT CURRENT_DATE FROM DUAL;

--8:    CURRENT_TIMESTAMP
以timestamp with time zone數據類型返回當前會話時區中的當前日期

SELECT CURRENT_TIMESTAMP FROM DUAL;

--9:   DBTIMEZONE()
返回時區

SELECT DBTIMEZONE FROM DUAL;

--10:   SESSIONTIMEZONE
返回回話時區  其中DBTIMEZONE是數據庫的，session是針對當前會話的，因為時區在會話級可以改變

SELECT SESSIONTIMEZONE FROM DUAL;

ALTER SESSION SET TIME_ZONE = '8:00';

SELECT SESSIONTIMEZONE FROM DUAL;

--11：EXTRACT
找出日期或間隔值的字段值

SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL;

SELECT EXTRACT(DAY  FROM SYSDATE)  AS "DAY" FROM DUAL;

SELECT EXTRACT(YEAR FROM SYSDATE) AS "YEAR" FROM DUAL;

--12：LOCALTIMESTAMP
返回會話中的日期和時間

SELECT LOCALTIMESTAMP FROM DUAL;

--13: TRUNC(for dates)

　　TRUNC函數為指定元素而截去的日期值。

　　其具體的語法格式如下：

　　TRUNC（date[,fmt]）

　　其中：

　　date 一個日期值

　　fmt 日期格式，該日期將由指定的元素格式所截去。忽略它則由最近的日期截去

　　下面是該函數的使用情況：

　　TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')

　　='24-Nov-1999 12:00:00 am'

　　TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'

　 SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回當年第一天.

　 SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回當月第一天.

　　SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回當前星期的第一天.

　　SELECT TRUNC(SYSDATE,'DD') FROM DUAL;--返回當前年月日

----  上月最后一天

SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL;

----: 上各月的今天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL;

---- 上個月第一天

SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL;

---  按照每周進行統計

SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;

---  按照每月進行統計

SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;

----  按照每季度進行統計

SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

---  按照每年進行統計

SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

---  要找到某月中所有周五的具體日期

SELECT TO_CHAR(T.D, 'YY-MM-DD')
FROM
(
    SELECT TRUNC(SYSDATE, 'MM') + ROWNUM -1 AS D
    FROM DBA_OBJECTS
    WHERE ROWNUM < 32
) T
WHERE TO_CHAR(T.D, 'MM') = TO_CHAR(SYSDATE, 'MM')
    AND TRIM(TO_CHAR(T.D, 'DAY')) = '星期五'

下面的內容應該屬于格式化函數，但是為了對日期函數做詳細敘述，就把TO_CHAR的內容放到這里了

Y或YY或YYY 年的最后一位，兩位或三位
SELECT TO_CHAR(SYSDATE, 'YYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL;

SYEAR或YEAR SYEAR使公元前的年份前加一負號
SELECT TO_CHAR(SYSDATE, 'SYEAR') FROM DUAL;    --TWENTY ELEVEN

Q 季度，1～3月為第一季度
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;        -- 2表示第二季度

MM 月份數
SELECT  TO_CHAR(SYSDATE, 'MM') FROM DUAL;      --04表示4月

RM 月份的羅馬表示
SELECT TO_CHAR(SYSDATE, 'RM') FROM DUAL;       --IV表示4月

Month 用9個字符長度表示的月份名
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL;    -- 4月

WW 當年第幾周
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;       -- 24表示2002年6月13日為第24周

W 本月第幾周
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;        -- 2011年04月26日為第4周

DDD 當年第幾天. 1月1日為001，2月1日為032
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

DD 當月第幾天
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

D 周內第幾天
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;

DY 周內第幾天縮寫
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;

HH或HH12 12進制小時數
SELECT TO_CHAR(SYSDATE, 'HH') FROM DUAL;

HH24 24小時制
SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL;

MI 分鐘數(0～59)
SELECT TO_CHAR(SYSDATE, 'MI') FROM DUAL;
提示注意不要將MM格式用于分鐘(分鐘應該使用MI)。MM是用于月份的格式，將它用于分鐘也能工作，但結果是錯誤的。

SS 秒數(0～59)
SELECT TO_CHAR(SYSDATE, 'SS') FROM DUAL;

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