Oracleでround関数(四捨五入)とtrunc関数(切り捨て) の使い方
1.数字操作
sql>select trunc(6.32), trunc(6.32,1), trunc(6.32,-1) from dual
—————-
trunc(6.32), trunc(6.32,1), trunc(6.32,-1)
6 6.3 0
sql>select round(6.32), round(6.32,1), round(6.32,-1) from dual
—————-
round(6.32), round(6.32,1), round(6.32,-1)
6 6.3 10
2.日付操作
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) now_Date,
to_char(round(sysdate),’yyyy-mm-dd hh24:mi:ss’) rdnow_Date,
to_char(trunc(sysdate),’yyyy-mm-dd hh24:mi:ss’) truncnow_Date from dual
—————-
now_Date rdnow_Date truncnow_Date
2016-09-02 11:00:09 2016-09-02 00:00:00 2016-09-02 00:00:00
3.trun関数のサンプル
①SELECT TRUNC(TO_DATE('2016-09-02 12:00:01′,’YYYY-MM-DD hh24:mi:ss’)) from DUAL
—————-
2016-09-02
②SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’D’)
FROM DUAL;
—————-
2016-08-28
③SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’D’)+1 FROM DUAL;
—————-
2016-08-29
④SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’MM’) FROM DUAL;
—————-
2016-09-01
⑤SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’MM’)-1 FROM DUAL;
—————-
2016-08-31
⑥SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’Y’) FROM DUAL;
—————-
16-01-01
⑧SELECT TRUNC(TO_DATE('2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’Y’)-1 FROM DUAL;
—————————————————————–
15-12-31
4.round関数のサンプル
select round(770.456, 0) from dual; –770
select round(770.456, 1) from dual; –770.5
select round(770.456, 2) from dual; –770.46
select round(770.456, 3) from dual; –770.456
select round(-770.456, 2) from dual; — -770.46