oracle中TO_CHAR和TO_DATE用法小结

TO_CHAR

• 用做日期转换 to_char(date,'格式')
  常用格式

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

eg:

select to_date('2005-01-01','yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

• 处理数字: to_char(number,'格式')
  eg:

select to_char(88877) from dual;  
select to_char(1234567890,'099999999999999')  from dual;  
SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL;  
SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL;  
select to_char(123456,'99.999')  from dual;  

• 用于进制转换: 将10进制转换为16进制
  eg:

select to_char(4567,'xxxx') from dual;
select to_char(123,'xxx') from dual;

TO_DATE

• 日期和字符转换函数用法 to_date(string,'格式')

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual;

eg:
求某天是星期几

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;     

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;   

SELECT TO_CHAR (TO_DATE (’19-oct-2018’), ‘fmDdthsp “of” Month, Year’) FROM DUAL

to_char和to_date在实际使用中,以混合使用居多,先按照要求把一个字符类型转换为date类型,然后使用to_char(date,'格式')函数转换为需要的展示方式.

references: 参考文档1 参考文档2

转载于:https://my.oschina.net/wangzongtao/blog/3080182