[SQL学习] 数字/时间处理函数

1. 保留指定小数位, 四舍五入
select round(1.4) from dual; # 1
select round(1.5) from dual; # 2
select round(1.2345, 3) from dual; # 1.235

2. 保留指定小数位, 截取
select trunc(1.4) from dual; # 1
select trunc(1.5) from dual; # 1
select trunc(1.2399, 2) from dual; # 1.23

3. 取整数, 小数位置有值则进位
select ceil(1) from dual; # 1
select ceil(1.0001) from dual; # 2

4. 取整数, 舍去小数位
select floor(1.4) from dual; # 1
select floor(1.9) from dual; # 1

5. 按照指定数字格式格式化数字, 四舍五入
# 9 补空格, 0 补 0
select to_char(123.4567,'99') from dual; # ## 整数位不足无法显示
select to_char(123.4567,'999') from dual; # 123
select to_char(123.4567,'999.9') from dual; # 123.5
select to_char(123.4567,'999.99') from dual; # 123.46
select to_char(123456.789,'999,999') from dual; # 123,457
select to_char(123.456,'9999.999') from dual; # (空格)123,457
select to_char(123.456,'9999.999') from dual; # (空格)123,457
select to_char(123.456,'0000.999') from dual; # 0123.456
select to_char(123.456,'999.9999') from dual; # 123.4560
select to_char(123.456,'999.0000') from dual; # 123.4560
select to_char(123.456,'999.9990') from dual; # 123.4560
select to_char(123.456,'$999.999') from dual; # $123.456
select to_char(8,'000') from dual; # (符号位空格)008
select to_char(8,'s000') from dual; # +008
select to_char(-8,'000') from dual; # -008
select to_char(8,'fm000') from dual; # 008, 去掉开头符号位空格

6. to_char函数特殊用法
to_char(sysdate,'d') # 每周第几天, 周日 = 1
to_char(sysdate,'dd') # 每月第几天
to_char(sysdate,'ddd') # 每年第几天
to_char(sysdate,'w') # 每月第几周
to_char(sysdate,'ww') # 每年第几周
to_char(sysdate,'mm') # 每年第几月
to_char(sysdate,'q') # 每年第几季
to_char(sysdate,'yy') # 年
to_char(sysdate,'yyyy') # 年