IT开放社区

TRUNC、ROUND截取单行数值函数

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>

 

相关文章

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:114
      • 页面总数:2
      • 分类总数:3
      • 标签总数:1
      • 评论总数:0
      • 浏览总数:43800
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

      分享:

      支付宝

      微信