TRUNC 数值截取
格式:TRUNC(x,y) x表示数值或数值列,y表示小数点后需保留的位数,不指定则无小数位;
itkaifang@SCOTT> SELECT TRUNC(987.654321,2) FROM dual; TRUNC(987.654321,2) ------------------- 987.65 itkaifang@SCOTT> SELECT TRUNC(987.654321,4) FROM dual; TRUNC(987.654321,4) ------------------- 987.6543 itkaifang@SCOTT> SELECT TRUNC(987.654321,-2) FROM dual; TRUNC(987.654321,-2) -------------------- 900 itkaifang@SCOTT>
TRUNC 截取日期
TRUNC函数处理日期时,没有fmt参数,默认截取到日;
itkaifang@SCOTT> SELECT SYSDATE FROM dual; SYSDATE ------------------- 2017-03-12 15:16:31 itkaifang@SCOTT> SELECT TRUNC(SYSDATE) FROM dual; TRUNC(SYSDATE) ------------------- 2017-03-12 00:00:00 itkaifang@SCOTT>
例:时间截取到小时、分钟;
itkaifang@SCOTT> SELECT TRUNC(SYSDATE,'hh'),TRUNC(SYSDATE,'mi') FROM dual; TRUNC(SYSDATE,'HH') TRUNC(SYSDATE,'MI') ------------------- ------------------- 2017-03-12 15:00:00 2017-03-12 15:22:00 itkaifang@SCOTT>
例:返回本周周一
itkaifang@SCOTT> SELECT TRUNC(SYSDATE,'iw') FROM dual; TRUNC(SYSDATE,'IW') ------------------- 2017-03-06 00:00:00 itkaifang@SCOTT>
例:返回本周周二
itkaifang@SCOTT> SELECT TRUNC(SYSDATE,'iw')+1 FROM dual; TRUNC(SYSDATE,'IW') ------------------- 2017-03-07 00:00:00 itkaifang@SCOTT>
例:返回本月第一天
itkaifang@SCOTT> SELECT TRUNC(SYSDATE,'mm') FROM dual; TRUNC(SYSDATE,'MM') ------------------- 2017-03-01 00:00:00 itkaifang@SCOTT>
例:返回本年第一天
itkaifang@SCOTT> SELECT TRUNC(SYSDATE,'y') FROM dual; TRUNC(SYSDATE,'Y') ------------------- 2017-01-01 00:00:00 itkaifang@SCOTT>
ROUND 数值截取(四舍五入)
语法:ROUND(x,y)用法同TRUNC;
itkaifang@SCOTT> SELECT ROUND(987.654321,2) FROM dual; ROUND(987.654321,2) ------------------- 987.65 itkaifang@SCOTT> SELECT ROUND(987.654321,1) FROM dual; ROUND(987.654321,1) ------------------- 987.7 itkaifang@SCOTT> SELECT ROUND(987.654321,-1) FROM dual; ROUND(987.654321,-1) -------------------- 990 itkaifang@SCOTT> SELECT ROUND(987.654321,-2) FROM dual; ROUND(987.654321,-2) -------------------- 1000 itkaifang@SCOTT>