Oracle中时间日期转化函数to_date和to_char的具体使用

目录

• 1、to_char()函数的用法
• 1.1、将时间日期按照指定的格式输出，得到的是字符串，而非date类型。
• 1.2、用to_char()可以得到日期中的年、月、日、时、分
• 使用实例
• 2、to_date()函数的用法
• 2.1、将字符串转换为具体指定的时间日期格式
• 2.2、可以直接使用date'yyyy-mm-dd'

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

 select sysdate,to_char(sysdate,'yyyy')from dual;
 select sysdate,to_char(sysdate,'mm')from dual;
 select sysdate,to_char(sysdate,'hh24')from dual;
 select sysdate,to_char(sysdate,'mi')from dual;

select accept_time,to_char(accept_time,'mi') from TMP_WW_0615_GYTS_S2 
where to_char(accept_time,'mi')='06' ;
select accept_time,to_char(accept_time,'mi') from TMP_WW_0615_GYTS_S2 
where to_char(accept_time,'mi')='6' ;

SQL>select to_char(sysdate,'YYYY-MM-DD HH12:MI:SS AM')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH1
2007-06-29 02:50:06 下午

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
2007-06-29 15:00:58 下午

select to_char(Sysdate-1/21/60,'HH24:MI:SS')from dual;
TO_CHAR(SYSDATE-1/21/60,'HH24:
15:00:54

select to_char(Sysdate+1/21/60,'HH24:MI:SS')from dual;
TO_CHAR(SYSDATE+1/21/60,'HH24:
15:03:53

select to_char(Sysdate-1/24,'HH24:MI:SS')from dual;
TO_CHAR(SYSDATE-1/24,'HH24:MI:
14:03:13

select to_char(Sysdate+1/24,'HH24:MI:SS')from dual;
TO_CHAR(SYSDATE+1/24,'HH24:MI:
16:03:32

select to_char(Sysdate+1,'YYYY-MM-DD')from dual;
TO_CHAR(SYSDATE+1,'YYYY-MM-DD'

select add_months(sysdate,-6) from dual

 select sysdate,to_date('20170615','yyyymmdd')from dual;
 select sysdate,to_date('20170615','yyyy-mm-dd')from dual;
 select sysdate,to_date('20170615','yyyy/mm/dd')from dual;
 select sysdate,to_date('20170615','yyyy-mm-dd hh24:mi:ss')from dual;

select date'2017-5-1',to_date('20170615','yyyymmdd')from dual;