to_data:日期转化成字符
select sysdate from dual;
yyyy:完整的数字年 mm:两位的数字月 dd: 完整的数字日
select to_char(sysdate,‘dd-mm-yyyy’) from dual;
hh: 2位数表示小时 12进制 mi: 2位数表示分钟 ss: 2位数表示秒
select to_char(sysdate,‘dd-mm-yyyy hh:mi:ss AM’) from dual;
hh24 2位数表示小时 24小时
select to_char(sysdate,‘dd-mm-yyyy hh24:mi:ss PM’) from dual;
Month:全长混合大小写月份名(9字符) month:全长小写月份名(9字符) 中文没看出来
select first_name,hire_date,to_char(hire_date,‘DD Month YYYY’),
to_char(hire_date,‘fmDD month YYYY’) from employees;
select to_char(sysdate,‘FMDD “日” Month YYYY’) from dual;
select to_char(sysdate,‘FMDDsp “日” Month YYYY’) from dual;
select to_char(sysdate,‘fmddth “日” Month YYYY’) from dual;
select to_char(sysdate,‘fmddspth “日” Month YYYY’) from dual;
年份的查询
select * from employees
where to_char(hire_date,‘yyyy’)=‘2003’;
select * from employees
where to_char(hire_date,‘mm’)=‘02’;
select * from employees
where to_char(hire_date,‘fmmm’)=‘2’;
to_data:数字转化成字符
select to_char(1500) from dual;
9:表示数字 数字位置
select to_char(1500,‘9,999’) from dual;
$:表示美元
select to_char(1500,’$9,999’) from dual;
select to_char(11500,’$9,999’) from dual;
.:显示小数点
select to_char(955.89,‘999.99’) from dual;
D:返回小数点
select to_char(955.89,‘999D99’) from dual;
0: 强制显示零 显示前导零
select to_char(955,‘099999’) from dual;
G: 在指定位置返回组分隔符(就是,) 可以在数字格式模型中指定多个组分隔符
select to_char(1987,‘9G999’) from dual;
综合应用
select to_char(1005.50 ,‘9,999.99’) from dual;
select to_char(1005.50 ,‘0,000.00’) from dual;
select to_char(1005.50 ,‘0G000D00’) from dual;
select to_char(1005.50 ,‘9G999D99’) from dual;
此强制显示零
select to_char(1005.50 ,‘09,999.99’) from dual;
select to_char(1005.50 ,‘99,999.99’) from dual;
如果用G或D, 那你就不能用了.或者,
select to_char(1005.50 ,‘9G999.99’) from dual;
select to_char(1005.50 ,‘9,999D99’) from dual;
to numner:将字符串转换为数字格式
select to_number(‘1,000’,‘9,999’) from dual;
select to_number(’$1,000’,’$9,999’) from dual;
select to_number(‘66’) from dual;
select to_number(‘1,987’,‘9G999’) from dual;
to_data:
select to_date(‘30-12-2019’,‘dd-mm-yyyy’) from dual;
select * from employees
where hire_date = to_date(‘07-03-2006’,‘dd-mm-yyyy’);
系统会删除那空格
select * from employees
where hire_date = to_date(‘07-03- 2006’,‘dd-mm-yyyy’);