oracle trunc函数用法

elivans

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

trunc ( date, [ format ] )

date is the date to truncate.

format is the unit of measure to apply for truncating. If the format parameter is omitted, the truncfunction will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

 

Below are the valid format parameters:

 

Unit
 Valid format parameters
 
Year
 SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
 
ISO Year
 IYYY, IY, I
 
Quarter
 Q
 
Month
 MONTH, MON, MM, RM
 
Week
 WW
 
IW
 IW
 
W
 W
 
Day
 DDD, DD, J
 
Start day of the week
 DAY, DY, D
 
Hour
 HH, HH12, HH24
 
Minute
 MI
 

 

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
 

For example:

 

trunc(to_date('22-AUG-03'), 'YEAR')
 would return '01-JAN-03'
 
trunc(to_date('22-AUG-03'), 'Q')
 would return '01-JUL-03'
 
trunc(to_date('22-AUG-03'), 'MONTH')
 would return '01-AUG-03'
 
trunc(to_date('22-AUG-03'), 'DDD')
 would return '22-AUG-03'
 
trunc(to_date('22-AUG-03'), 'DAY')
 would return '17-AUG-03'
 

 

 

  --Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.458
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120