oracle tochar 时分秒,【Oracle日常之】to_date()、to_char()

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;

总结

写的有点乱，先这样吧。