trunc()函数使用

  --Oracle trunc()函数的用法
 
/**************日期********************/
/*
  TRUNC(date[,fmt])
  date 时间
  fmt 日期格式，不支持组合时间格式，如yyyy-MM-dd等格式
  没有fmt,则默认为当前日期，没有时间
 
*/
--1. 2013-11-26  今天的日期为
select trunc(sysdate) from dual; 

--2.2013-11-1    返回当月第一天.
select trunc(sysdate, 'mm')   from   dual; 

--3.2013-1-1       返回当年第一天
select trunc(sysdate,'yy') from dual; 

--4.2013-11-26    返回当前年月日
select trunc(sysdate,'dd') from dual; 

--5.2013-1-1   返回当年第一天
select trunc(sysdate,'yyyy') from dual; 

--6. 2013-11-24 (星期天)返回当前星期的第一天
select trunc(sysdate,'d') from dual; 

--7.2013-11-26 11:00:00  返回当前时间，到小时
select trunc(sysdate, 'hh') from dual; 

--8.2013-11-26 11:27:00   TRUNC()函数没有秒的精确   
select trunc(sysdate, 'mi') from dual;
 
--9.2013-11-26 11:00:00  返回当前时间，到小时（24小时制）
select trunc(sysdate, 'hh24') from dual;

/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
10.select trunc(123.458) from dual --123
11.select trunc(123.458,0) from dual --123
12.select trunc(123.458,1) from dual --123.4
13.select trunc(123.458,-1) from dual --120
14.select trunc(123.458,-4) from dual --0
15.select trunc(123.458,4) from dual  --123.458
16.select trunc(123) from dual  --123
17.select trunc(123,1) from dual --123
18.select trunc(123,-1) from dual --120

 

oracle 时间格式

Oracle DateFormat

 

Format element	Description
AD   A.D.	AD indicator.
AM   A.M.   PM   P.M.	Meridian indicator.
BC   B.C.	BC indicator.
CC	Century
SCC	Century BC prefixed with -
D	Day of week: 1-7.
DAY	Name of day.
DD	Day of month: 1-31.
DDD	Day of year: 1-366.
DL	Date in long date format.
DS	Date in short date format.
DY	Abbreviated name of day.
E	Abbreviated era name.
EE	Full era name.
FF[1..9]	Fractional seconds. Example: Format ‘MI.SS.FF2′
FM	Returns a value without blanks (leading and trailing).
FX	Format exact.
HH	Hour of day: 1-12.
HH24	Hour of day: 0-23.
IW	Week of year: 1-53 by ISO standard.
I   IY   IYY   IYYYY	Last 1, 2, 3 or 4 digits of year by ISO standard.
J	Number of days since January 1, 4712 BC – Julian day.
MI	Number of minutes: 1-59.
SS	Number of seconds: 1-59.
SSSSS	Number of seconds past midnight: 0-86399.
MM	Month: 01-12.
MON	Abbreviated name of month.
MONTH	Month name.
Q	Quarter of year: 1-4.
RM	Roman numeral month: I-XII.
RR   RRRR	Century dates using 2 or 4 digits.
TS	Returns a short time format.
TZD	Daylight savings information
TZH	Time zone: hour.
TZM	Time zone: minute.
TZR	Time zone: region information.
W   WW	Week of month: 1-5.   Week of year: 1-53.
X	Local radix char.
YEAR	Year in words, like:  TWENTY ELEVEN
Y   YY   YYY   YYYY	Last 1, 2, 3 or 4 digits of year.