to_date()
最常用的用法就是将字符串转化为timestamp类型的日期
如果小时大于12，就认为是24小时时间格式，需要用HH24，否则会报错，如下
SELECT SYSDATE,TO_DATE('2019-03-20 23:14:12','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
执行结果如下：
需要注意的是， 由于是转化为timestamp类型，所以我们看到的结果格式都是和系统日期保持一致，YYYY-MM-DD HH24:MI:SS只是起到解析前面字符串的作用,
YYYY、MM、DD、HH24、MI、SS分别对应年、月、日、时、分、秒，只可多匹配不可少匹配。
下面的写法都正确
SELECT SYSDATE,TO_DATE('2019-3-20','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT SYSDATE,TO_DATE('20190320231412','YYYYMMDDHH24MISS') FROM DUAL;
SELECT SYSDATE,TO_DATE('2019 03 20 23 14 12','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT SYSDATE,TO_DATE('20190320','YYYYMMDDHH24MISS') FROM DUAL;
SELECT SYSDATE,TO_DATE('2019-03-20','YYYY/MM/DD') FROM DUAL;
SELECT SYSDATE,TO_DATE('20190320','YYYY-MM-DD') FROM DUAL;
to_char()
to_char()函数的作用是将数字或时间转化为指定格式的字符串
将数字转化为指定格式的字符串
语法格式如下：
TO_CHAR(n:NUMBER [, fmt:VARCHAR, nlsparam:VARCHAR]])
该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'用来指定小数点和千分位分隔符以及货币的符号，如下：
'NLS_NUMERIC_CHARACTERS=., NLS_CURRENCY=￥ NLS_ISO_CURRENCY=CNY'
NLS_NUMERIC_CHARACTERS=.,表示指定小数位分隔符为. 千分位分隔符为,
NLS_CURRENCY=￥表示指定本地货币符号为￥
NLS_ISO_CURRENCY=CNY表示指定ISO货币符号为CNY
下面给出各种转换情况的实例
-
0与9：
每一个0或9都表示一个有效位，转换值的有效位与0或9的各位相同。负数有前导负号，前导为0时，使用0，保持不变，使用9则视为空格。如果精度减小，自动四舍五入。
SELECT TO_CHAR(0.456,'0.9999') FROM DUAL; -- 结果：' 0.4560' 前有一个空格
SELECT TO_CHAR(0.456,'9.9999') FROM DUAL; -- 结果：' .4560' 前有一个空格
SELECT TO_CHAR(0.456,'0.99') FROM DUAL; -- 结果：' 0.46' 前有一个空格，发生了四舍五入
-- 用FM来去除前后无用的空格和0
SELECT TO_CHAR(0.456,'FM0.9999') FROM DUAL; -- 结果：'0.456'
-
千分位与小数位：
逗号","：在指定位置返回一个逗号（可以不在千分位位置）。不能与G，D等格式元素同时使用
G：返回千分位分隔符，不一定是","，要看Oracle数据库的默认设置或nlsparams的值。
句点"."：在指定位置返回一个句点（可以不在小数位位置）。不能与G，D等格式元素同时使用
D：返回小数位分隔符，不一定是"."，要看Oracle数据库的默认设置或nlsparams的值
TO_CHAR(3333333333.66666,'FM9,999,999,999.99999') --结果：'3,333,333,333.66666'
TO_CHAR(3333333333.66666,'FM9G999G999G999D99999') --结果：'3,333,333,333.66666'
// 下面自定义千分位与小数位的符号
TO_CHAR(3333333333.66666,'FM9G999G999G999D99999','NLS_NUMERIC_CHARACTERS=+-') --结果： '3-333-333-333+66666'
- 货币符号：
$：TO_CHAR(0.345,'FM$90.999') --结果：'$0.345'
L：本地货币符号，TO_CHAR(37,'L99') --结果：'￥37'，即NLS_CURRENCY的值
// 自定义本地货币符号:TO_CHAR(37,'L99','NLS_CURRENCY=$') --结果：'$37
C'：ISO货币符号，TO_CHAR(22,'FMC99') --结果：'ANY22'，即NLS_ISO_CURRENCY的值
// 自定义ISO货币符号:TO_CHAR(22,'FMC99','NLS_ISO_CURRENCY=USD') --结果：'USD22'
-
MI：
后继负号（只能为后缀）：
值为负，加后继负号，否则加后继占位符
TO_CHAR(-222,'999MI') --结果：'222-'
-
S：
S在前，为前导+或-号，在后，为后继+或-号
TO_CHAR(222,'S999') --结果：'+222'
TO_CHAR(222,'999S') --结果：'222+'
-
PR：
为负数，就用尖括号扩起来，正数前导后继各加一个空格
TO_CHAR(-222,'999PR') --结果：'<222>'
TO_CHAR(-222,'999PR') --结果：' 222 '
-
数字 * 10的n次方
V：返回与10的n次方相乘的值，n是V后面9的个数
TO_CHAR(22,'99V9999') --结果：' 220000'
-
EEEE：
科学计数法
TO_CHAR(121.976,'99.99EEEE') --结果：' 1.22E+02'
-
返回罗马数字：
RM：返回大写
rm：返回小写
TO_CHAR(5,'FMrm') --结果：'v'
TO_CHAR(5,'FMRM') --结果：'V'
将时间转化为指定格式的字符串
首先得清楚日期格式参数的含义：
YEAR：年，英文拼写，如2019为TWENTY NINETEEN
IYYY: ISO标准的四位年份
YYYY: 四位年份
YYY: 年份的最后三位
YY: 年份的最后两位
Y: 年份的最后一位
Q：季度（1, 2, 3, 4）
MM: 月,例子：05
Mon: 月份的简写，例子：5月
Month: 同Mon
RM：罗马数字月
W: 该月的第几个星期
WW: 该年中的第几个星期
IW: ISO标准的年中的第几周
D: 一周中的星期几，注意星期天是第一天
DAY: 当天是星期几
DY: 同DAY
DD：该月中的第几天
DDD：该年中的第几天
HH: 小时，按12小时计
HH24: 小时，按24小时计
MI: 分
SS: 秒
SSSSS：当天零点到现在经过的秒数
SSxFF: 毫秒
FM：前缀，可以在参数前面，会去掉前后无用的空格和0
例如to_char(sysdate, 'MM')结果是05，如果加上FM变成to_char(sysdate, 'FMMM')，结果就是5
to_char()将日期date转化为VARCHAR2字符串的语法格式如下：
to_char({datetime:TIMESTAMP | intervval:INTERVAL} [,fmt:VARCHAR [, nlsparam:VARCHAR]])
format缺省日期格式为Oracle的缺省日期格式（NLS_DATE_FORMAT）。
结果字符串的大小写风格：可以用Year，year，YEAR，Mon等这类format字符串控制。
分隔符：-（中杠）/（斜杠）,（逗号）.（句点）;（分号）:（冒号）等。
可以使用引号""来指定任何文本作为分隔符。
nlsparams指定返回字符串所使用的语言，可以修改session的NLS_DATE_LANGUAGE改变其缺省值。
修改方法：alter session set NLS_DATE_LANGUAGE='AMERICAN'
如果不使用默认值，可使用如下格式：'NLS_DATE_LANGUAGE=language'
下面是例子展示：
- 得到当前时间，24小时制
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') --结果：'2019-05-20 16:18:41'
- 12小时制，自动根据时间判断是午前还是午后，午前（AM | A.M.）或午后（PM | P.M.）：
TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS AM') --结果：'2019-05-20 04:24:41 下午'
- 公元（AD | A.D.）或公元前（BC | B.C.）：自动根据日期判断是AD还是BC
TO_CHAR(SYSDATE,'YYYY-MM-DD AD') --结果：'2019-05-20 公元'
- CC与SCC：世纪数，使用SCC时，公元前为负数，公元后为正数；使用CC结果为SCC的绝对值
TO_CHAR(SYSDATE,'CC') --结果：'21'
TO_CHAR(SYSDATE,'SCC') --结果：' 21'
- 获取时间信息
// 获取当前月份
TO_CHAR(SYSDATE,'D') -- 结果: '2'
// 获取当前是星期几
TO_CHAR(SYSDATE,'DAY') -- 结果: '星期一'
// 获取当前季度
TO_CHAR(SYSDATE,'Q') -- 结果：'2' 表示第二季度
// 返回当前时间年月日小时分秒毫秒
TO_CHAR(CURRENT_TIMESTAMP(5),'YYYY-MM-DD HH24:MI:SSXFF') -- 结果：'2019-05-20 08:41:10.64257'
//--返回当前时间的秒毫秒，可以指定秒后面的精度(最大=9)
TO_CHAR(CURRENT_TIMESTAMP(9),'MI:SSXFF') -- 结果：'42:09.382861000'
上面只给出少量例子，可以根据不同的日期格式参数得到更多时间信息
- 时间的运算
获取当前时间减去5分钟的时间
方式1： SELECT SYSDATE,SYSDATE - 1/24/12 FROM DUAL;
方式2： SELECT SYSDATE,SYSDATE - INTERVAL '5' MINUTE FROM DUAL;
获取当前时间减去5小时的时间
方式1： SELECT SYSDATE,SYSDATE - 1/24*5 FROM DUAL;
方式2： SELECT SYSDATE,SYSDATE - INTERVAL '5' HOUR FROM DUAL;
获取当前时间减去5天的时间
方式1： SELECT SYSDATE,SYSDATE - 1*5 FROM DUAL;
方式2： SELECT SYSDATE,SYSDATE - INTERVAL '5' DAY FROM DUAL;
获取当前时间减去一周的时间
方式1： SELECT SYSDATE,SYSDATE - 7 FROM DUAL;
方式2： SELECT SYSDATE,SYSDATE - 7*INTERVAL '1' DAY FROM DUAL;
获取当前时间减去5个月的时间
方式1： 不能用
方式2： SELECT SYSDATE,SYSDATE - INTERVAL '5' MONTH FROM DUAL;
获取当前时间减去5年的时间
方式1： 不能用
方式2： SELECT SYSDATE,SYSDATE - INTERVAL '5' YEAR FROM DUAL;
总结
写的有点乱，先这样吧。
网友评论