TO_CHAR  数值转字符语法：

TO_CHAR(number,'format_model')

TO_CHAR  数值转字符用法；

centos@SCOTT> SELECT TO_CHAR(123.456,'99999.99') AS num FROM  dual;

NUM
---------
   123.46

centos@SCOTT>

9表示一个数字，如小数点后显示的位数小于实际数字，那么在显示时会四舍五入。

使用fm去除空格；

centos@SCOTT> SELECT REPLACE(TO_CHAR(123.456,'99999.99'),' ','#') AS num FROM  dual;

NUM
---------
###123.46

centos@SCOTT> SELECT REPLACE(TO_CHAR(123.456,'fm99999.99'),' ','#') AS num FROM dual;

NUM
---------
123.46

centos@SCOTT>

使用0补齐要求宽度；

centos@SCOTT> SELECT TO_CHAR('123','09999999') AS zero FROM dual;

ZERO
---------
 00000123

centos@SCOTT>

TO_CHAR 日期转字符语法：

TO_CHAR（date,'format_model')

TO_CHAR 日期转字符用法；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;

DT
-----------------------

2017-04-01 12:23:43

centos@SCOTT>

使用fm去除前导零；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') AS dt FROM dual;

DT
-------------------
2017-4-1 12:26:5

centos@SCOTT>

使用scc或cc显示世纪；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'scc yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;

DT
-----------------------
21 2017-04-01 12:27:30

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'cc yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;

DT
----------------------
21 2017-04-01 12:27:57

centos@SCOTT>

使用yyyy显示年；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'yyyy') AS dt FROM dual;

DT
----
2017

centos@SCOTT>

使用year显示英语单词拼写的年；

centos@SCOTT>  SELECT TO_CHAR(SYSDATE,'year') AS year FROM dual;

YEAR
------------------------------------------
twenty seventeen

centos@SCOTT>

使用q显示当前年的第几季度；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'q') AS q,SYSDATE FROM dual;

Q SYSDATE
- ----------
2 2017-04-01

centos@SCOTT>

使用ww显示一年中第几周，w显示一个月中第几周；

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'ww') AS ww,SYSDATE FROM dual;

WW SYSDATE
-- ----------
13 2017-04-01

centos@SCOTT> SELECT TO_CHAR(SYSDATE,'w') AS w,SYSDATE FROM dual;

W SYSDATE
- ----------
1 2017-04-01

centos@SCOTT>