oracle中trunc()和to_char()函数用法

-----trunc(for date)

select sysdate from dual; --当前时间  2016/9/7 10:32:04
select trunc(sysdate) from dual;--取当天  2016/9/7
select trunc(sysdate,'yyyy') from dual; --取当年第一天  2016/1/1
select trunc(sysdate,'mm') from dual; --取当月第一天  2016/9/1
select trunc(sysdate,'dd') from dual; --取当天 2016/9/7
select trunc(sysdate,'d') from dual; --返回当前星期的第一天  2016/9/4
select trunc(sysdate,'hh') from dual; --返回当前时间，精确到小时  2016/9/7 10:00:00
select trunc(sysdate,'mi') from dual; --返回当前时间，精确到分钟 2016/9/7 10:32:00
--trunc(for number)
select trunc(2016.11) from dual; --2016
select trunc(2016.99) from dual; --2016
select trunc(2016.99,1) from dual; --2016.9
select trunc(2016.99,3) from dual; --2016.99
select trunc(2016.99,-1) from dual; --2010
select trunc(2016.99,-2) from dual; --2000
select trunc(2016.99,-4) from dual; --0
select trunc(2016,1) from dual; --2016
select trunc(2016,-1) from dual; --2010

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; --日期转化为字符串  2016-09-07 10:33:11
select to_char(sysdate,'yyyy') as nowYear from dual; --获取时间的年 2016
select to_char(sysdate,'mm') as nowMonth from dual; --获取时间的月 09
select to_char(sysdate,'dd') as nowDay from dual; --获取时间的日 07
select to_char(sysdate,'hh24') as nowHour from dual; --获取时间的时 10
select to_char(sysdate,'mi') as nowMinute from dual; --获取时间的分 33
select to_char(sysdate,'ss') as nowSecond from dual; --获取时间的秒 11
select to_char(sysdate,'day') as nowDay from dual; --获取当天是星期几 星期三
select to_char(sysdate,'D') as nowDay from dual; --获取当天是星期几 4
select floor(sysdate - to_date('2016-08-05','yyyy-mm-dd')) from dual; --取两个日期间的天数 33

 

转载于:https://www.cnblogs.com/gexiaoshan/p/5848536.html