SQL> select trunc(sysdate,'w') a from dual;
A
-----------
9/8/2009
SQL> select trunc(sysdate,'year') from dual;
TRUNC(SYSDATE,'YEAR')
---------------------
1/1/2009
这里的 TRUNC(SYSDATE,'W') 为什么是 8号?按我的理解应该是本周的第一天SQL> select trunc(sysdate,'ww') from dual;
TRUNC(SYSDATE,'WW')
-------------------
9/10/2009
还有这个,没理解通
A
-----------
9/8/2009
SQL> select trunc(sysdate,'year') from dual;
TRUNC(SYSDATE,'YEAR')
---------------------
1/1/2009
这里的 TRUNC(SYSDATE,'W') 为什么是 8号?按我的理解应该是本周的第一天SQL> select trunc(sysdate,'ww') from dual;
TRUNC(SYSDATE,'WW')
-------------------
9/10/2009
还有这个,没理解通
SQL> select sysdate,trunc(sysdate,'dd') from dual;
SYSDATE TRUNC(SYSDATE,'DD')
----------- -------------------
2009-09-10 2009-09-10
SQL> --截取本周第一天:
SQL> select sysdate,trunc(sysdate,'d') from dual;
SYSDATE TRUNC(SYSDATE,'D')
----------- ------------------
2009-09-10 2009-09-06
SQL> --截取本月第一天:
SQL> select sysdate,trunc(sysdate,'mm') from dual;
SYSDATE TRUNC(SYSDATE,'MM')
----------- -------------------
2009-09-10 2009-09-01
SQL> --截取本年第一天:
SQL> select sysdate,trunc(sysdate,'y') from dual;
SYSDATE TRUNC(SYSDATE,'Y')
----------- ------------------
2009-09-10 2009-01-01
SQL> --截取到小时:
SQL> select sysdate,trunc(sysdate,'hh') from dual;
SYSDATE TRUNC(SYSDATE,'HH')
----------- -------------------
2009-09-10 2009-09-10 12:00:00
SQL> --截取到分钟:
SQL> select sysdate,trunc(sysdate,'mi') from dual;
SYSDATE TRUNC(SYSDATE,'MI')
----------- -------------------
2009-09-10 2009-09-10 12:02:00
SQL>
CC
SCC
One greater than the first two digits of a four-digit yearSYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
Year (rounds up on July 1)IYYY
IY
IY
I
ISO YearQ
Quarter (rounds up on the sixteenth day of the second month of the quarter)MONTH
MON
MM
RM
Month (rounds up on the sixteenth day)WW
Same day of the week as the first day of the yearIW
Same day of the week as the first day of the ISO yearW
Same day of the week as the first day of the monthDDD
DD
J
DayDAY
DY
D
Starting day of the weekHH
HH12
HH24
HourMI
Minute
WW
Same day of the week as the first day of the year W
Same day of the week as the first day of the month
英语我是看的一知半解的。。不过感觉这个解释只适合 TO_CHAR不适合TRUNC
Same day of the week as the first day of the year
和该年第一天所在的星期里一样的天。
比如2009-01-01是星期四
所以这里返回的是本周四对于的时间,所以返回的是10号
SQL> select trunc(sysdate,'WW') a from dual;
A
--------------
10-9月 -09W
Same day of the week as the first day of the month
和本月的第一天在星期里一样的天数
SQL> select trunc(sysdate,'W') a from dual;
本月是2009-09-01是星期二
所以返回的是本周的星期二
也就是
A
--------------
08-9月 -09