ORACLE單行函式與多行函式之四：日期函式示例

實驗環境：

BYS@bys1>select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER            VALUE
-------------------- ------------------------------
NLS_DATE_FORMAT      yyyy/mm/dd hh24:mi:ss

BYS@bys1>show parameter nls_lang
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

nls_language                         string      AMERICAN

1.直接使用SYSDATE加減數字來操作日期

日期+或-1,都代表加減一天的時間；而如果是一小時或幾分鐘這種，可以用天/小時這種方法。

如下面語句，1小時是1/24；5分鐘是1/24/12。86400：1天=24小時=24*60*60=86400秒

BYS@bys1>select sysdate+365,sysdate-1,sysdate-3,sysdate-1/24,sysdate-1/24/12 from dual;
SYSDATE+365         SYSDATE-1           SYSDATE-3           SYSDATE-1/24        SYSDATE-1/24/12
------------------- ------------------- ------------------- ------------------- -------------------

2014/11/02 19:26:15 2013/11/01 19:26:152013/10/30 19:26:15 2013/11/0218:26:15 2013/11/0219:21:15

2.TIMESTAMP 記錄了年、月、日、時、分、秒和納秒

SYSTIMESTAMP返回的是TIMESTAMP WITH TIME ZONE 型別的資料。+08:00表示當前是東八區。
BYS@bys1>select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
02-NOV-13 09.08.04.390741 PM +08:00
timestamp的顯示格式不同於SYSDATE，要重新指定。
BYS@bys1>alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
Session altered.

BYS@bys1>select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
02-NOV-13 09.11.19.258161 PM +08:00

表示TIMESTAMP的方法：
–to_timestamp('2013-02-09 23:59:59.000','yyyy-mm-dd hh24:mi:ss.ff')
–timestamp '2013-04-05 13:48:00.123456789'
–to_timestamp中的分隔符可以更換， timestamp中的日期分隔符必須是-，時間必須是:，秒後面必須跟上.
–timestamp可以精確表示到毫秒、微秒甚至納秒級別
轉換時未指定值時的預設值：年：同SYSDATE裡的年；月：同SYSDATE裡的月；日：1號；時分秒和納秒：均為0
BYS@bys1>col a3 for a30
BYS@bys1>col a2 for a30
BYS@bys1>col a1 for a30
BYS@bys1>select to_timestamp('05 13','YY HH24') as a1,to_timestamp('05 13','mm mi') as a2,to_timestamp('05 13','dd ss') as a3 from dual;
A1                             A2                             A3
------------------------------ ------------------------------ ------------------------------
2005-11-01 13:00:00.000000000  2013-05-01 00:13:00.000000000  2013-11-05 00:00:13.000000000
關於微秒的指定方式：FF5表示給的時間戳可以有不超過5位的微秒。如果時間戳微秒有3位，指定轉換為FF2，則報錯。

同時在秒後最多隻能指定9位。
BYS@bys1>select to_timestamp('05 13:48:22.778','DD HH24:MI:SS.FF5') from dual;
TO_TIMESTAMP('0513:48:22.778','DDHH24:MI:SS.FF5')
---------------------------------------------------------------------------
2013-11-05 13:48:22.778000000

要注意

BYS@bys1>select to_timestamp('05 13:48:22.778','DD HH24:MI:SS.FF2') from dual;
select to_timestamp('05 13:48:22.778','DD HH24:MI:SS.FF2') from dual
                    *
ERROR at line 1:
ORA-01880: the fractional seconds must be between 0 and 999999999

BYS@bys1>select to_timestamp('05 13:48:22.123456789','DD HH24:MI:SS.FF9') from dual;
TO_TIMESTAMP('0513:48:22.123456789','DDHH24:MI:SS.FF9')
---------------------------------------------------------------------------
2013-11-05 13:48:22.123456789
BYS@bys1>select to_timestamp('05 13:48:22.1234567890','DD HH24:MI:SS.FF9') from dual;
select to_timestamp('05 13:48:22.1234567890','DD HH24:MI:SS.FF9') from dual
                    *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
BYS@bys1>select to_timestamp('05 13:48:22.1234567890','DD HH24:MI:SS.FF10') from dual;
select to_timestamp('05 13:48:22.1234567890','DD HH24:MI:SS.FF10') from dual
                                             *
ERROR at line 1:
ORA-01821: date format not recognized

3.date函式只可以表示日期，不可以表示時間。在下面4中有應用示例。

預設值：年：同SYSDATE裡的年；月：同SYSDATE裡的月；日：1號；時分秒：均為0

4.判斷指定日期是否是某一天的。to_date及date中如果只指定日期未指定時間，預設是0點0分0秒。即前一天23:59:59的下一秒。

注意BETWEEN AND 相當於大於等於和小於等於。所以屬於某一天，嚴格來說應該是從當天0點的0秒到 當天23:59:59秒。1天除以86400即1秒

BYS@bys1> select 'TRUE' from dual where to_date('2013-11-02 21:48:22','YYYY-MM-DD HH24:MI:SS') between date'2013-11-01' and date'2013-11-06'-1/86400;

'TRU
----
TRUE

BYS@bys1>select 'TRUE' from dual where to_date('2013/11/02 21:45:43','YYYY-MM-DD HH24:MI:SS') between to_date('2013-11-02','yyyy-mm-dd') and  to_date('2013-11-03','yyyy-mm-dd hh24:mi:ss')-1/86400;

'TRU
----
TRUE
也可以用to_date對日期進行顯式轉換。
select 'TRUE' from dual where to_date('2013/11/02 21:45:43','YYYY-MM-DD HH24:MI:SS') between to_date('2013-11-02','yyyy-mm-dd') and  to_date('2013-11-03','yyyy-mm-dd hh24:mi:ss')-1/86400;
 
'TRUE'
------
TRUE

注意BETWEEN AND 相當於大於等於和小於等於

BYS@bys1> select 'TRUE' from dual where to_date('2013/11/02 21:45:43','YYYY-MM-DD HH24:MI:SS') >= date'2013-11-02' and to_date('2013/11/02 21:45:43','YYYY-MM-DD HH24:MI:SS') <= date'2013-11-03'-1/86400;
'TRU
----
TRUE
其實也可以用小於11月3號來表示小於等於11月2號的23：59：59秒。

BYS@bys1> select 'TRUE' from dual where to_date('2013/11/02 23:59:59','YYYY-MM-DD HH24:MI:SS') >= date'2013-11-02' and to_date('2013/11/02 23:59:59','YYYY-MM-DD HH24:MI:SS') < date'2013-11-03';
'TRU
----
TRUE

5.MONTHS_BETWEEN(a,b):表示a和b兩個日期的月份之差，是a-b，如果a日期比b晚，即比b大，則為正數；反之，為負數。

BYS@bys1>Select EMPNO,HIREDATE,MONTHS_BETWEEN(Sysdate,HIREDATE)/12 dday,sysdate From EMP where rownum<3;
     EMPNO HIREDATE                  DDAY SYSDATE
---------- ------------------- ---------- -------------------
      7369 1980/12/17 00:00:00 32.8784294 2013/11/02 18:37:05
      7499 1981/02/20 00:00:00 32.7036983 2013/11/02 18:37:05
BYS@bys1>select months_between(sysdate,to_date('2011/11/11 11:11:11','yyyy-mm-dd hh24:mi:ss')) as dday from dual;
      DDAY
----------
23.7196307
BYS@bys1>select months_between(to_date('2011/11/11 11:11:11','yyyy-mm-dd hh24:mi:ss'),sysdate) as dday from dual;
      DDAY
----------
-23.719639

6.ADD_MONTHS:表示給指定的日期加一個月數，即N個月後的日期。如果當前日期加上指定月數超過一年，則年份也自動增加。

BYS@bys1>select add_months(sysdate,1),add_months(sysdate,4) from dual;
ADD_MONTHS(SYSDATE, ADD_MONTHS(SYSDATE,
------------------- -------------------

2013/12/02 18:39:23 2014/03/02 18:39:23

7.NEXT_DAY:表示以當前時間為基準,下一個"目標日"的日期

BYS@bys1>select next_day(sysdate,'sunday'),next_day(sysdate,'tuesday') from dual;
NEXT_DAY(SYSDATE,'S NEXT_DAY(SYSDATE,'T
------------------- -------------------

2013/11/03 19:34:20 2013/11/05 19:34:20

8.LAST_DAY:計算當前日期的最後一天，即當月最後一天。

BYS@bys1>select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------

2013/11/30 18:43:16

9.使用ROUND:對日期進行四捨五入

只能對年、月、日、時、分進行四捨五入；不能操作秒。
BYS@bys1>select round(sysdate,'yy') as year,round(sysdate,'mm') as month,round(sysdate,'dd') as day,round(sysdate,'hh') as hour,round(sysdate,'hh24') as hour24,round(sysdate,'mi') as minutes  from dual;
YEAR                MONTH               DAY                 HOUR                HOUR24              MINUTES
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2014/01/01 00:00:00 2013/11/01 00:00:00 2013/11/03 00:00:00 2013/11/02 19:00:00 2013/11/02 19:00:00 2013/11/02 18:59:00
BYS@bys1> select round(sysdate,'ss') as sss from dual;
 select round(sysdate,'ss') as sss from dual
                      *
ERROR at line 1:

ORA-01899: bad precision specifier

10.使用TRUNC:對日期進行擷取

BYS@bys1>set linesize 200
BYS@bys1>select trunc(sysdate,'yy') as year,trunc(sysdate,'mm') as month,trunc(sysdate,'dd') as day,trunc(sysdate,'hh') as hour,trunc(sysdate,'hh24') as hour24,trunc(sysdate,'mi') as minutes  from dual;
YEAR                MONTH               DAY                 HOUR                HOUR24              MINUTES
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2013/01/01 00:00:00 2013/11/01 00:00:00 2013/11/02 00:00:00 2013/11/02 18:00:00 2013/11/02 18:00:00 2013/11/02 18:52:00
只能擷取年、月、日、時、分；不能擷取秒。
BYS@bys1> select trunc(sysdate,'ss') as sss from dual;
 select trunc(sysdate,'ss') as sss from dual
                      *
ERROR at line 1:
ORA-01899: bad precision specifier