oracle字符串类型的时间常用操作

实际使用中，我们在数据库中存储的都是字符串【number（14）,number（8）】，日期类型的很少使用，相信这也是很多公司都是如此。
好处囊，前台取展示等无需转化，无需使用特定组件等。
不便之处囊，编辑页面需特定控件去转译，日期的常规操作遇到闰年闰月什么的就很难受。
当然这些不便之处可以通过日期处理工具类一劳永逸解决。

Oracle操作
常规的日期转化，24小时制
sql:
select kbsj,
TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’),
TO_CHAR(TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’), ‘YYYY-MM-DD HH24:MI:SS’) dfjssj
from date_test;

12小时制
select kbsj,
TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’),
TO_CHAR(TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’), ‘YYYY|"年"MM|"月"DD|“日” HH12:MI:SS’) dfjssj
from date_test;

–转化为年月日
第一种
这个是自己之前写的，现在看起来挺low的，不过依旧记录下。
select sl.lcfqsj, (substr(sl.lcfqsj,0,4)||‘年’||substr(sl.lcfqsj,5,2)||‘月’||substr(sl.lcfqsj,7,2)||‘日’) cjsj from 表名;
第二种
下面的都是可以的
通过拼接来实现自己可以发挥，懂了拼接那么你就可以玩的花里胡哨的啦。😄
select kbsj,
TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’),
TO_CHAR(TO_DATE(kbsj, ‘YYYYMMDDHH24MISS’),
–‘YYYY|"年"MM|"月"DD|“日” HH24:MI:SS’
‘YYYY"年"MM"月"DD"日" HH24:MI:SS’
) dfjssj
from date_test;

经常会有一些特殊的字符无法存储，这个时候需要去转译
1 ASCII码对应表
2 在ORACLE中，单引号有两个作用，一是字符串是由单引号引用，二是转义。单引号的使用是就近配对，即就近原则。
3 连接符‘||’导致了新一轮的转义：连接符号‘||’左右的单引号没有任何的关系，除非‘||’是作为字符串的一部分（这在动态SQL中很常见）。

select chr(38),‘test’ || ‘’’’ , ‘test ‘’’ from dual;

这个使用我一般使用在封装好的功能模块，为了拓展功能，则将许多需要执行的sql放入某个字段中，这个时候会有一种情况所需的脚本中包含了需要转译的字符参照如下例子
update tj_lclb
set ywsql = ‘select czy.mc ,
(to_char(sysdate,’||chr(39)||‘yyyy"年"mm"月"dd"日"’||chr(39)||’)) spcjsj ,
xm.* from xm_zbxm xm ,gg_czyb czy where xm.xmjbr = czy.id and xm.id=:id
’

update test_info set pageurl=‘test.jsp?page=1’||’&’||‘size=10’ where id=‘jsp’；
update test_info set pageurl=‘test.jsp?page=1’||chr(38)||‘size=10’ where id=‘jsp’；

insert 配合 select
insert into jc_gysdj
(id, gysid, gysmc)
select sys_guid(),
id,
gsmc,
'from jc_gysxx;

Update 配合 select
update tj_zffs t set qcr=(select nr.jsrbh from gzyjnr nr where t.htbh = nr.ywbh);

从别人那搬来的
不推荐
Oracle日期增加年/月/日/时/分/秒
–增加一年
SELECT SYSDATE + INTERVAL ‘1’ YEAR FROM DUAL;
–增加一月
SELECT SYSDATE + INTERVAL ‘1’ MONTH FROM DUAL;
–增加一天
SELECT SYSDATE + INTERVAL ‘1’ DAY FROM DUAL;
–增加1小时
SELECT SYSDATE + INTERVAL ‘1’ HOUR FROM DUAL;
–增加1分钟
SELECT SYSDATE + INTERVAL ‘1’ MINUTE FROM DUAL;
–增加1秒钟
SELECT SYSDATE + INTERVAL ‘1’ SECOND FROM DUAL;

推荐：
ORACLE 日期加减操作
无论是DATE还是timestamp都可以进行加减操作。
可以对当前日期加年、月、日、时、分、秒，操作不同的时间类型，有三种方法：

1 使用内置函数numtodsinterval增加小时，分钟和秒
2 加一个简单的数来增加天
3 使用内置函数add_months来增加年和月

例：
对当前日期增加一个小时：
SQL> select sysdate, sysdate+numtodsinterval(1,’hour’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:38:19 2010-10-14 22:38:19
对当前日期增加50分种
SQL> select sysdate, sysdate+numtodsinterval(50,’minute’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
对当前日期增加45秒
SQL> select sysdate, sysdate+numtodsinterval(45,’second’) from dual ;

SYSDATE SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51
对当前日期增加3天
SQL> select sysdate, sysdate+3 from dual ;

SYSDATE SYSDATE+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46
对当前日期增加4个月
SQL> select sysdate, add_months(sysdate,4) from dual ;

SYSDATE ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43

当前日期增加2年
SQL> select sysdate, add_months(sysdate,12*2) from dual ;

SYSDATE ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17

timestamp的操作方法与上面类似；
求两个日期之差：
例：求2007-5-23 21：23：34与当前时间之间的差值。
SQL> select sysdate-to_date(’20070523 21:23:34′,’yyyy-mm-dd hh24:mi:ss’) dt from
dual ;

DT
———-
1240.01623
如果两个日期直接相减，得到的结果是一个数据型，我们可能想要得到两个日期相差值表现形式为：
年月日 ::
SQL> SELECT NUMTOYMINTERVAL(MONTHS_BETWEEN(DT1, DT2), ‘month’) mon,
numtodsinterval(dt1-(add_months(dt2,trunc(MONTHS_BETWEEN(DT1, DT2)))
),’day’) DAY
FROM (SELECT SYSDATE DT1,
TO_DATE(’20070523 21:23:34′, ‘yyyy-mm-dd hh24:mi:ss’) DT2
FROM DUAL) ;

MON DAY
—————- ———————-
+000000003-04 +000000021 00:40:15.999999999

即：3年 4 个月 21 天 00:40:15.99999999

注： Chr(0) Null Chr(29) 分组符 Chr(38) & Chr(48) 0 Chr(8) 退格 Chr(30) 记录分割符号 Chr(39) ‘ Chr(49) 1 Chr(9) Tab Chr(31) 单元分割符号 Chr(40) ( Chr(50) 2 Chr(10) 换行 Chr(32) 空格SPACE Chr(41) ) Chr(51) 3 Chr(12) 换页 Chr(33) ! Chr(42) * Chr(52) 4 Chr(13) Enter Chr(33) ! Chr(43) + Chr(53) 5 Chr(22) 粘贴 Chr(34) “ Chr(44) , Chr(54) 6 Chr(26) 撤销 Chr(35) # Chr(45) – Chr(55) 7 Chr(27) Esc Chr(36) $ Chr(46) . Chr(56) 8 Chr(28) 文件分隔符 Chr(37) % Chr(47) / Chr(57) 9 Chr(58) : Chr(68) D Chr(78) N Chr(88) X Chr(59) ; Chr(69) E Chr(79) O Chr(89) Y Chr(60) < Chr(70) F Chr(80) P Chr(90) Z Chr(61) = Chr(71) G Chr(81) Q Chr(91) [ Chr(62) > Chr(72) H Chr(82) R Chr(92) \ Chr(63) ? Chr(73) I Chr(83) S Chr(93) ] Chr(64) @ Chr(74) J Chr(84) T Chr(94) ^ Chr(65) A Chr(75) K Chr(85) U Chr(95) _ Chr(66) B Chr(76) L Chr(86) V Chr(96) Chr(67) C Chr(77) M Chr(87) W Chr(97) a Chr(98) b Chr(108) l Chr(118) v Chr(128) € Chr(99) c Chr(109) m Chr(119) w Chr(100) d Chr(110) n Chr(120) x Chr(101) e Chr(111) o Chr(121) y Chr(102) f Chr(112) p Chr(122) z Chr(103) g Chr(113) q Chr(123) { Chr(104) h Chr(114) r Chr(124) | Chr(105) i Chr(115) s Chr(125) } Chr(106) j Chr(116) t Chr(126) ~ Chr(107) k Chr(117) u Chr(127) 删除