Oracle转换函数-日期字符数字转换

Oracle转换函数-日期字符数字转换

• Year:yy 16,yyy 016,yyyy 2036
• Month:mm 11, mon 11月/nov, month 11月/november
• Day:dd当月第几天02,ddd当年第几天02,dy当周第几天星期五/fri,day当周第几天星期五/friday
• Hour:hh12小时进制01，hh2424小时进制13
• 24小时的时间范围：0:00:00-23:59:59,12小时时间范围:1:00:00-12:59:59
• Minute:mi60进制45
• Second:ss60进制25
• 其它:Q季度4，WW当年第几周44，W当月第几周1

(1) to_date 字符串>日期类型

select to_date('2036-12-18 19:02:02','YYYY-MM-DD HH24:MI:SS') T from dual;
select to_date('2036-12-18 19:02','YYYY-MM-DD HH24:MI') T from dual;
select to_date('2036-12-18 19','YYYY-MM-DD HH24') T from dual;
select to_date('2036-12-18','YYYY-MM-DD') T from dual;
select to_date('2036-12','YYYY-MM') T from dual;
select to_date('2036','YYYY') t from dual;
select to_date('20361218','YYYY-MM-DD') T from dual;

(2) to_char 日期/数字>字符串

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'hh24') from dual;
select to_char('23432.4') from dual;
select to_char(sysdate,'yyyymmdd') from dual;
select to_char(sysdate,'mmdd') from dual;
select to_char(sysdate,'yyyymm') from dual;

(3) to_number 字符>数字

select sysdate-(to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss')) from dual;
select to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss')) from dual;
select to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss'))/365 from dual;
select trunc(to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss'))/365) from dual;
select to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss'))*24 from dual;
select to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss'))*24*60 from dual;
select to_number(sysdate-to_date('2018-11-02 15:59:59','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual;