[20170414]round(sysdate,'day').txt

[20170414]round(sysdate,'day').txt

http://www.itpub.net/thread-2086507-1-1.html

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select sysdate S1, round(sysdate) S2 , round(sysdate,'day') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-14 16:15:09 2017-04-15 00:00:00 2017-04-16 00:00:00

SCOTT@book> select sysdate-8/24 S1, round(sysdate-8/24) S2 , round(sysdate-8/24,'day') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-14 08:15:37 2017-04-14 00:00:00 2017-04-16 00:00:00

SCOTT@book> select sysdate-8/24 S1, round(sysdate-8/24) S2 , round(sysdate-8/24,'hh') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-14 08:20:55 2017-04-14 00:00:00 2017-04-14 08:00:00

SCOTT@book> select sysdate S1, round(sysdate) S2 , round(sysdate,'hh24') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-14 16:21:10 2017-04-15 00:00:00 2017-04-14 16:00:00

SCOTT@book> select sysdate S1, round(sysdate) S2 , round(sysdate,'hh24') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-14 16:34:50 2017-04-15 00:00:00 2017-04-14 17:00:00

--//自己开始也感觉很奇怪，round(sysdate,'day') 中 day表示天，这样向上，向下取最多是2017-04-15 00:00:00，而不会是2017-04-16 00:00:00
--//而我取时间又是正确的。你可以发现这个采用类似四舍五入的算法。

--//仔细看官方文档：
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions242.htm#SQLRF52037
格式      表示
--------- ---------
DDD DD J  day
DAY DY D  Starting day of the week

-- DAY 表示 Starting day of the week。

SCOTT@book> select sysdate-2 S1, round(sysdate-2) S2 , round(sysdate-2,'day') DAY from dual;
S1                  S2                  DAY
------------------- ------------------- -------------------
2017-04-12 16:38:26 2017-04-13 00:00:00 2017-04-16 00:00:00

--//看来以后看文档要仔细一些。我个人建议使用trunc，估计很少人使用round。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2137294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2137294/