1 year=1*12 months
1 day=24 hours=24*(1*60mins)=24*60*(1*60 seconds)
1 week =7 days
current_timestamp
datimezone
months_between
next_day
round
SYSDATE
trunc()
B:上个月的今天
select add_months(sysdate,-1) from dual --在月份上减少
C: 上个月的最后一天
select last_day(add_months(sysdate,-1)) from dual
--------------- ---------------
20051109 160535 20051109 160534 B:
--------------- ---------------
20051109 030644 20051109 160644
---------------------------------------------------------------------------
09-NOV-05 04.27.41.416811 PM +08:00
B: use precision
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- --------------------------------------------------
-05:00 09-NOV-05 03.33.32.595277 AM -05:00
C: You can user others date funtion on current_timestamp
--------
20051109
-----------------------------------------------
2005
B:
------------------------------------------------
11 6: Last_day
--------- --------- ----------
10-NOV-05 30-NOV-05 20
-.09677419 -3 (11 月只有30 天)
2 BST, BDT: Bering Standard or Daylight Time
3 CST, CDT: Central Standard or Daylight Time
4 EST, EDT: Eastern Standard or Daylight Time
5 GMT: Greenwich Mean Time
6 HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
7 MST, MDT: Mountain Standard or Daylight Time
8 NST: Newfoundland Standard Time
9 PST, PDT: Pacific Standard or Daylight Time
10 YST, YDT: Yukon Standard or Daylight Time
------------------- -------------------
2005-11-10 06:24:06 2005-11-10 10:24:06
--------------------------
05-11-14 10:48:01
Format Mask
Rounds or Truncates to
CC or SSC
Century
SYYY, YYYY, YEAR, SYEAR, YYY, YY, or Y
Year (rounds up to next year on July 1)
IYYY, IYY, IY, or I
Standard ISO year
Q
Quarter (rounds up on the sixteenth day of the second month of the quarter)
MONTH, MON, MM, or RM
Month (rounds up on the sixteenth day, which is not necessarily the same as the middle of the month)
WW
Same day of the week as the first day of the year
IW
Same day of the week as the first day of the ISO year
W
Same day of the week as the first day of the month
DDD, DD, or J
Day
DAY, DY, or D
Starting day of the week
HH, HH12, HH24
Hour
MI
Minute
Example:
Round up to the next century:
Round back to the beginning of the current century:
Round down and up to the first of the year:
Round up and down to the quarter (first date in the quarter):
Round down and up to the first of the month:
Day of first of year is Saturday:
So round to date of nearest Saturday for `01-MAR-1994':
First day in the month is a Friday:
So round to date of nearest Friday from April 16, 1994:
使用To_char 和Round 组合显示日期:
Round back to nearest day (time always midnight):
Round forward to the nearest day:
Round back to the nearest hour:
FROM DUAL;
本月的第一天
select trunc(sysdate,'month') from dual
select trunc(sysdate,'year') from dual
本月的最后一天
select last_day(sysdate) from dual
本周的第一天
select trunc(sysdate,'day') from dual --Oracle default start week is Sunday
select trunc(sysdate,'iw') from dual ---ISO year default start week is Monday
本周的星期一
select trunc(sysdate,'day')+1 from dual --2,3,4,5,6,
经常会用到的一些Trunc 函数例子(默认的日期格式是DD-MON-YYYY);
Without a format mask, TRUNC sets the time to 12:00 A.M. of the same day:
Trunc to the beginning of the century in all cases:
Trunc to the first of the current year:
Trunc to the first day of the quarter:
Trunc to the first of the month:
TO_Char 函数和Trunc 函数的一些组合:
Trunc back to the beginning of the current day (time is always midnight):
Trunc to the beginning of the current hour:
------
+00:00
YEAR
MONTH
DAY
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34596/viewspace-809730/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/34596/viewspace-809730/