日期:2014-05-16  浏览次数:20503 次

ORACLE时间计算相关函数整理
  1. trunc()函数;
  2. round()函数;
  3. EXTRACT()函数;
  4. to_char()函数;
  5. interval 间隔计算;

?

-- 获取时间

?

?

select sysdate "sysdate" from dual;
sysdate
-------------------------
2013/8/5 10:20:14

?

?

?

-- 日期精确到 年 月 日 时 分 秒 季度 世纪

-- trunc() & round() 这两个函数经常被使用在数值处理中,日期类型中同样适用。

TRUNC(date [, fmt ])
ROUND(date [, fmt ])

fmt列表如下:

?

Table 5-14?lists the format models you can use with the?ROUND?and?TRUNC?date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 5-14?Date Format Models for the ROUND and TRUNC Date Functions

Format Model Rounding or Truncating Unit
CC
SCC

One greater than the first two digits of a four-digit year

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

Year (rounds up on July 1)

IYYY
IY
IY
I

ISO Year

Q

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 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
J

Day

DAY
DY
D

Starting day of the week

HH
HH12
HH24

Hour

MI

Minute