oracle trunc to_char

select to_char(sysdate,'yyyy') from dual; --年

select to_char(sysdate,'Q' from dual; --季

select to_char(sysdate,'mm') from dual; --月

select to_char(sysdate,'dd') from dual; --日

ddd 年中的第几天

WW 年中的第几个星期

W 该月中第几个星期

D 周中的星期几

hh 小时(12)

hh24 小时(24)

Mi 分

ss 秒

 

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

 

 

see also:

         http://www.2cto.com/database/201109/102477.html

         http://www.cnblogs.com/leeyvie/archive/2010/01/08/1642416.html