oracle 精确到秒的格式串

to_date('2004-3-24 15:59:9','YYYY-MM-DD HH24:MI:SS')

出处：http://www.hkln.net/doc/oracle/sql/function.htm#a8

簡介

下一節到頁頂

Oracle 提供了一些基本的函數，用來運算資料，這些函數可分為以下幾類：

文字
數目
日期
其它類別。

本章所介紹的函數都是運算一行的資料，所以稱為「單行函數」 (Single-Row Functions) ，另外有一種叫做「分組函數」 (Group Functions) ，用來運算多行的資料，分組函數會在稍後的篇章介紹。

至於詳細的函數說明，請參考 Oracle 的 SQL Reference 文件。

DUAL 表格

之前介紹的 SELECT 句子的 FROM 子句，都是寫一個資料庫的表格名稱，例如 EMP ，不過有時我們會用資料庫來做些簡單的運算，例如計算 1+2 ，這根本不需要使用任何表格，那麼， FROM 子句應該寫什麼呢？答案就是 DUAL 。

SQL> SELECT 1 + 2 FROM DUAL;

       1+2
----------
         3

因為每個 SELECT 句子都必需有 FROM 子句，所以 Oracle 就做個 DUAL 表格來符合這個守則了。

DUAL 表格還可以用來提供一些資訊，例如使用者名稱、系統時間等。

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
03-NOV-02

數目函數

函數	傳回的資料
ABS(X)	絕對值
ROUND(X, Y)	X 的四捨五入值，如果 Y 是正數，就取小數位的右邊，負數就取左邊
CEIL(X)	與 ROUND(X, 0) 差不多，不過它會 Round Up
FLOOR(X)	與 CEIL(X) 差不多，不過它會 Round Down
MOD(X, Y)	X 除以 Y 的餘數
SIGN(X)	如果 X 是正數，就傳回 1 ，負數就傳回 -1 ， 0 就傳回 0
SQRT(X)	平方根
TRUNC(X, Y)	與 ROUND(X, Y) 差不多，不過它是 Truncation

例子：

SQL> SELECT ABS(2), ABS(-2) FROM DUAL

    ABS(2)    ABS(-2)
---------- ----------
         2          2

SQL> SELECT ROUND(1.5, 0), ROUND(0.15, 1), ROUND(15, -1) FROM DUAL;

ROUND(1.5,0) ROUND(0.15,1) ROUND(15,-1)
------------ ------------- ------------
           2            .2           20

SQL> SELECT CEIL(1.4), CEIL(1.5), CEIL(-1.4), CEIL(-1.5) FROM DUAL;

 CEIL(1.4)  CEIL(1.5) CEIL(-1.4) CEIL(-1.5)
---------- ---------- ---------- ----------
         2          2         -1         -1

SQL> SELECT FLOOR(1.4), FLOOR(1.5), FLOOR(-1.4), FLOOR(-1.5) FROM DUAL;

FLOOR(1.4) FLOOR(1.5) FLOOR(-1.4) FLOOR(-1.5)
---------- ---------- ----------- -----------
         1          1          -2          -2

SQL> SELECT MOD(4, 2), MOD(4,3), MOD(4,5) FROM DUAL;

  MOD(4,2)   MOD(4,3)   MOD(4,5)
---------- ---------- ----------
         0          1          4

SQL> SELECT SIGN(2), SIGN(-2), SIGN(0) FROM DUAL;

   SIGN(2)   SIGN(-2)    SIGN(0)
---------- ---------- ----------
         1         -1          0

SQL> SELECT SQRT(4), SQRT(2) FROM DUAL;

   SQRT(4)    SQRT(2)
---------- ----------
         2 1.41421356

SQL> SELECT TRUNC(12.34, 0), TRUNC(12.34, 1), TRUNC(12.34, -1) FROM DUAL;

TRUNC(12.34,0) TRUNC(12.34,1) TRUNC(12.34,-1)
-------------- -------------- ---------------
            12           12.3              10

文字函數

函數	傳回的資料
LPAD(X, Y [,Z])	把 X 的左邊加入字元 Z （預設的字元是空格），令它的長度成為 Y 。
RPAD(X, Y [,Z])	同上，把 X 的右邊加入字元 Z 。
LOWER(X)	把 X 所有字元變成小寫
UPPER(X)	把 X 所有字元變成大寫
INITCAP(X)	把 X 的每個英文字的第一個字元變成大寫，其它字元變成小寫。
LENGTH(X)	X 的長度
SUBSTR(X, Y [,Z])	由 X 的第 Y 個字元開始，抽取 Z 個字元（預設抽取所有字元）。
INSTR(X, Y)	Y 在 X 的位置。
CONCAT(X, Y)	把 X 和 Y 連接。也可以用 \|\| （兩條直線）運算子。

例子：

SQL> SELECT LPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'

LPAD(ENAME,10,'=')
---------------------
=====SCOTT

SQL> SELECT RPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'

RPAD(ENAME,10,'=')
---------------------
SCOTT=====

SQL> SELECT LOWER(ENAME) FROM EMP WHERE ENAME = 'SCOTT';

LOWER(ENAM
----------
scott

SQL> SELECT INITCAP(ENAME) FROM EMP WHERE ENAME = 'SCOTT';

INITCAP(EN
----------
Scott

SQL> SELECT LENGTH(ENAME) FROM EMP WHERE ENAME = 'SCOTT';

LENGTH(ENAME)
-------------
            5

SQL> SELECT SUBSTR(ENAME, 2, 3) FROM EMP WHERE ENAME = 'SCOTT';

SUBSTR
------
COT

SQL> SELECT INSTR(ENAME, 'COT') FROM EMP WHERE ENAME = 'SCOTT';

INSTR(ENAME,'COT')
------------------
                 2

SQL> SELECT ENAME || '_' || JOB || '_' || SAL FROM EMP WHERE ENAME = 'SCOTT';

ENAME||'_'||JOB||'_'||SAL
-------------------------------------------------------------
SCOTT_ANALYST_3000

說明：

SQL> SELECT SUBSTR(ENAME, 2, 3) FROM EMP WHERE ENAME = 'SCOTT';

SUBSTR
------
COT

留意第一個字元的位置是 1 ，而不是 0 。

NVL 函數

NVL 函數可以把 NULL 值轉換成另一些資料。假設你想計算 EMP 表格內的每個僱員的總薪金，而總薪金的計法是 SAL + COMM ，你可能會寫：

SQL> SELECT ENAME, SAL, COMM, (SAL + COMM) TOTAL_SALARY FROM EMP;

ENAME             SAL       COMM TOTAL_SALARY
---------- ---------- ---------- ------------
SMITH             800
ALLEN            1600        300         1900
WARD             1250        500         1750
JONES            2975
MARTIN           1250       1400         2650
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500          0         1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

為什麼有些行的 TOTAL_SALARY 是空的呢？因為它們的 COMM 是 NULL ，而 NULL 加上數目都會是 NULL 。

解決方法就是把 NULL 值轉換成 0 ，那就不會得出 NULL 了。

SQL> SELECT ENAME, SAL, NVL(COMM, 0), (SAL + NVL(COMM, 0)) TOTAL_SALARY FROM EMP;

ENAME             SAL NVL(COMM,0) TOTAL_SALARY
---------- ---------- ----------- ------------
SMITH             800           0          800
ALLEN            1600         300         1900
WARD             1250         500         1750
JONES            2975           0         2975
MARTIN           1250        1400         2650
BLAKE            2850           0         2850
CLARK            2450           0         2450
SCOTT            3000           0         3000
KING             5000           0         5000
TURNER           1500           0         1500
ADAMS            1100           0         1100
JAMES             950           0          950
FORD             3000           0         3000
MILLER           1300           0         1300

14 rows selected.

說明：

NVL(COMM, 0)

如果 COMM 是 NULL ，就傳回 0 ，否則傳回 COMM 。

DECODE 函數

DECODE 函數的功能有點像程式語言的 if-then-else 句子，它會能夠根據資料不同的值，傳回不同的資料，語法如下：

DECODE(COLUMN_NAME
    ,VALUE1, SUBSTITUTE1
    ,VALUE2, SUBSTITUTE2
    , ...
    ,DEFAULT
)

如果 COLUMN_NAME 的值是 VALUE1 ，就傳回 SUBSTITUTE1 ， VALUE2 ，就傳回 SUBSTITUTE2 ，如此類推，如果以上的值都不是，就傳回 DEFAULT 。

其實 DECODE 也可以做到 NVL 的功能，例如 NVL(COMM, 0) 可以寫成：

DECODE(COMM, NULL, 0, COMM)

假設你想列出每個僱員的部門名稱，除了可以把 EMP 連接到 DEPT 外，還可以用 DECODE 來做。

SQL> SELECT ENAME
  2      ,DECODE(DEPTNO
  3          ,10 ,'Accounting'
  4          ,20 ,'Research'
  5          ,30 ,'Sales'
  6          ,40 ,'Opeartions'
  7          ,'UNKNOWN'
  8      ) DEPARTMENT
  9   FROM EMP
 10   ;

ENAME      DEPARTMENT
---------- -------------
SMITH      Research
ALLEN      Sales
WARD       Sales
JONES      Research
MARTIN     Sales
BLAKE      Sales
CLARK      Accounting
SCOTT      Research
KING       Accounting
TURNER     Sales
ADAMS      Research
JAMES      Sales
FORD       Research
MILLER     Accounting

14 rows selected.

日期函數

Oracle 是用數目來儲存日期資料的，數目的整數部份代表與 Julian Calendar 相距的日數（由公元前 4712 年 1 月 1 日開始），而小數部份代表時、分和秒。

例子：

SQL> SELECT SYSDATE ,SYSDATE - 1 YESTERDAY ,SYSDATE + 1 TOMORROW
  2  FROM DUAL;

SYSDATE   YESTERDAY TOMORROW
--------- --------- ---------
03-NOV-02 02-NOV-02 04-NOV-02

說明：

示範把日期當作數目來運算

函數	傳回的資料
ADD_MONTHS(X, Y)	X 的 Y 個月後的日期
LAST_DAY(X)	X 所屬月份的最後一日
MONTHS_BETWEEN(X, Y)	X 與 Y 之間相距多少個月，如果 X 比 Y 少，傳回的是一個負數。
NEXT_DAY(X, Y)	X 的下一個星期 Y

例子：

SQL> SELECT ADD_MONTHS('3-NOV-02', 2) FROM DUAL;

ADD_MONTH
---------
03-JAN-03

SQL> SELECT LAST_DAY('3-NOV-02') FROM DUAL;

LAST_DAY(
---------
30-NOV-02

SQL> SELECT MONTHS_BETWEEN('3-NOV-02', '1-OCT-02') FROM DUAL;

MONTHS_BETWEEN('3-NOV-02','1-OCT-02')
-------------------------------------
                           1.06451613

SQL> SELECT NEXT_DAY('2-NOV-02', 'TUESDAY') FROM DUAL;

NEXT_DAY(
---------
05-NOV-02

日期函數 : TO_CHAR 函數

你可以透過 TO_CHAR 函數，把日期資料轉換成指定的文字格式。

例子：

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
03-NOV-02

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'DD-
--------------------
03-NOV-2002 19:52:49

說明：

TO_CHAR 函數的第二個參數是轉換的格式：

格式字元	說明
D	星期幾（ 1 至 7 ）
DD	月的第幾日（ 1 至 31 ）
MON	月（簡寫）
MONTH	月（全寫）
YY	年（ 2 個數位）
YYYY	年（ 4 個數位）
RR	年（ 2 個數位，「千禧年相容」 (Millennium-Compliant) ）
RRRR	年（ 4 個數位，「千禧年相容」 (Millennium-Compliant) ）
HH	時（ 2 個數位）
HH24	時（ 2 個數位， 24 小時制）
MI	分（ 2 個數位）
SS	秒（ 2 個數位）

日期函數 : TO_DATE 函數

上一節到頁頂

TO_DATE 函數是用來把文字轉換成日期的，例如當你在 SQL 中表示一個日期，可以寫：

SQL> SELECT TO_DATE('03-NOV-02', 'DD-MON-RR') FROM DUAL;

TO_DATE('
---------
03-NOV-02

第一個參數是文字內容，第二個參數是該文字內容的格式， Oracle 會跟據這個格式來把文字轉成日期。

假設你想知道 EMP 表格中，有哪些僱員是於 1987 年 1 月 1 日或以後受僱的，可以寫：

SQL> SELECT ENAME, HIREDATE FROM EMP
  2  WHERE TO_DATE('01-JAN-1987', 'DD-MON-YYYY') <= HIREDATE
  3  ;

ENAME      HIREDATE
---------- ---------
SCOTT      19-APR-87
ADAMS      23-MAY-87

因為 HIREDATE 是日期，所以必需用 TO_DATE 把你寫在 SQL 的文字轉成日期，才能作出比較。

如果你沒有輸入第二個參數，那麼 Oracle 就會使用預設的日期格式，它是儲存在一個名叫 NLS_DATE_FORMAT 的參數，你可以在表格 V$NLS_PARAMETERS 取得：

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

SQL> SELECT ENAME, HIREDATE FROM EMP
  2  WHERE TO_DATE('01-JAN-1987') <= HIREDATE
  3  ;

ENAME      HIREDATE
---------- ---------
SCOTT      19-APR-87
ADAMS      23-MAY-87

由此可見，預設的格式為 DD-MON-RR ，所以就算 TO_DATE 沒有第二個參數也沒有問題。

posted on 2005-01-14 18:00 涛仔28 阅读(784) 评论(0) 编辑收藏举报

刷新页面返回顶部

梁某人

导航

公告

oracle 精确到秒的格式串