轉(zhuǎn)載blog.csdn.net/haiross/article/details/12837033

TRUNC函數(shù)用于對(duì)值進(jìn)行截?cái)唷?br>

用法有兩種：TRUNC（NUMBER）表示截?cái)鄶?shù)字喷户，TRUNC（date）表示截?cái)嗳掌凇?/p>

(1)截?cái)鄶?shù)字：

格式：TRUNC（n1,n2），n1表示被截?cái)嗟臄?shù)字耍休，n2表示要截?cái)嗟侥且晃弧2可以是負(fù)數(shù)仅仆，表示截?cái)嘈?shù)點(diǎn)前缎玫。注意，TRUNC截?cái)嗖皇撬纳嵛迦搿?/p>

SQL> select TRUNC(15.79) from dual;

TRUNC(15.79)

------------

15

SQL> select TRUNC(15.79,1) from dual;

TRUNC(15.79,1)

--------------

15.7

SQL> select trunc(15.79,-1) from dual;

TRUNC(15.79,-1)

---------------

10

(2)截?cái)嗳掌冢?/p>

先執(zhí)行命令：alter session set nls_date_format='yyyy-mm-dd hh24:mi:hh';

截取今天：

SQL> select sysdate,trunc(sysdate,'dd') from dual;

SYSDATE??????????? ?TRUNC(SYSDATE,'DD')

------------------- -------------------

2009-03-24 21:31:17 2009-03-24 00:00:00

截取本周第一天：

SQL> select sysdate,trunc(sysdate,'d') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'D')

------------------- -------------------

2009-03-24 21:29:32 2009-03-22 00:00:00

截取本月第一天：

SQL> select sysdate,trunc(sysdate,'mm') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'MM')

------------------- -------------------

2009-03-24 21:30:30 2009-03-01 00:00:00

截取本年第一天：

SQL> select sysdate,trunc(sysdate,'y') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'Y')

------------------- -------------------

2009-03-24 21:31:57 2009-01-01 00:00:00

截取到小時(shí)：

SQL> select sysdate,trunc(sysdate,'hh') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'HH')

------------------- -------------------

2009-03-24 21:32:59 2009-03-24 21:00:00

截取到分鐘：

SQL> select sysdate,trunc(sysdate,'mi') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'MI')

------------------- -------------------

2009-03-24 21:33:32 2009-03-24 21:33:00

獲取上月第一天：

SQL> select TRUNC(add_months(SYSDATE,-1),'MM') from dual

===================================================================

--Oracletrunc()函數(shù)的用法

/**************日期********************/

1.select trunc(sysdate) from dual--2011-3-18? 今天的日期為2011-3-18

2.select trunc(sysdate, 'mm')?? from?? dual--2011-3-1??? 返回當(dāng)月第一天.

3.select trunc(sysdate,'yy') from dual--2011-1-1?????? 返回當(dāng)年第一天

4.select trunc(sysdate,'dd') from dual--2011-3-18??? 返回當(dāng)前年月日

5.select trunc(sysdate,'yyyy') from dual--2011-1-1?? 返回當(dāng)年第一天

6.select trunc(sysdate,'d') from dual--2011-3-13 (星期天)返回當(dāng)前星期的第一天

7.select trunc(sysdate, 'hh') from dual--2011-3-18 14:00:00?? 當(dāng)前時(shí)間為14:41

8.select trunc(sysdate, 'mi') from dual--2011-3-18 14:41:00?? TRUNC()函數(shù)沒有秒的精確

/***************數(shù)字********************/

/*

TRUNC（number,num_digits）

Number 需要截尾取整的數(shù)字悬槽。

Num_digits 用于指定取整精度的數(shù)字悔常。Num_digits 的默認(rèn)值為 0影斑。

TRUNC()函數(shù)截取時(shí)不進(jìn)行四舍五入

*/

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

========================================================================================================

oracle trunc(sysdate ,'dd') 日期

oracle

[sql]view plaincopy

TRUNC函數(shù)用于對(duì)值進(jìn)行截?cái)唷?br>

用法有兩種：TRUNC（NUMBER）表示截?cái)鄶?shù)字，TRUNC（date）表示截?cái)嗳掌凇?/p>

(1)截?cái)鄶?shù)字：

格式：TRUNC（n1,n2）机打，n1表示被截?cái)嗟臄?shù)字矫户，n2表示要截?cái)嗟侥且晃弧2可以是負(fù)數(shù)残邀，表示截?cái)嘈?shù)點(diǎn)前吏垮。注意，TRUNC截?cái)嗖皇撬纳嵛迦搿?/p>

SQL> select TRUNC(15.79) from dual;

TRUNC(15.79)

------------

15

SQL> select TRUNC(15.79,1) from dual;

TRUNC(15.79,1)

--------------

15.7

SQL> select trunc(15.79,-1) from dual;

TRUNC(15.79,-1)

---------------

10

(2)截?cái)嗳掌冢?/p>

先執(zhí)行命令：alter session set nls_date_format='yyyy-mm-dd hh24:mi:hh';

截取今天：

SQL> select sysdate,trunc(sysdate,'dd') from dual;

SYSDATE??????????? ?TRUNC(SYSDATE,'DD')

------------------- -------------------

2009-03-24 21:31:17 2009-03-24 00:00:00

截取本周第一天：

SQL> select sysdate,trunc(sysdate,'d') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'D')

------------------- -------------------

2009-03-24 21:29:32 2009-03-22 00:00:00

截取本月第一天：

SQL> select sysdate,trunc(sysdate,'mm') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'MM')

------------------- -------------------

2009-03-24 21:30:30 2009-03-01 00:00:00

截取本年第一天：

SQL> select sysdate,trunc(sysdate,'y') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'Y')

------------------- -------------------

2009-03-24 21:31:57 2009-01-01 00:00:00

截取到小時(shí)：

SQL> select sysdate,trunc(sysdate,'hh') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'HH')

------------------- -------------------

2009-03-24 21:32:59 2009-03-24 21:00:00

截取到分鐘：

SQL> select sysdate,trunc(sysdate,'mi') from dual;

SYSDATE???????????? TRUNC(SYSDATE,'MI')

------------------- -------------------

2009-03-24 21:33:32 2009-03-24 21:33:00

獲取上月第一天：

SQL> select TRUNC(add_months(SYSDATE,-1),'MM') from dual

===================================================================

--Oracletrunc()函數(shù)的用法

/**************日期********************/

1.select trunc(sysdate) from dual--2011-3-18? 今天的日期為2011-3-18

2.select trunc(sysdate, 'mm')?? from?? dual--2011-3-1??? 返回當(dāng)月第一天.

3.select trunc(sysdate,'yy') from dual--2011-1-1?????? 返回當(dāng)年第一天

4.select trunc(sysdate,'dd') from dual--2011-3-18??? 返回當(dāng)前年月日

5.select trunc(sysdate,'yyyy') from dual--2011-1-1?? 返回當(dāng)年第一天

6.select trunc(sysdate,'d') from dual--2011-3-13 (星期天)返回當(dāng)前星期的第一天

7.select trunc(sysdate, 'hh') from dual--2011-3-18 14:00:00?? 當(dāng)前時(shí)間為14:41

8.select trunc(sysdate, 'mi') from dual--2011-3-18 14:41:00?? TRUNC()函數(shù)沒有秒的精確

/***************數(shù)字********************/

/*

TRUNC（number,num_digits）

Number 需要截尾取整的數(shù)字罐旗。

Num_digits 用于指定取整精度的數(shù)字膳汪。Num_digits 的默認(rèn)值為 0。

TRUNC()函數(shù)截取時(shí)不進(jìn)行四舍五入

*/

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

========================================================================================================

oracle trunc(sysdate ,'dd') 日期

oracle

[sql]view plaincopy

selecttrunc(sysdate?,'dd')fromdual?;--??2007-9-19

selecttrunc(sysdate?,'yyyy')fromdual?;--2007-1-1

selecttrunc(sysdate?,'mm')fromdual?;--2007-9-1

begin

dbms_output.put_line(?to_char?(?(sysdate)????,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line(?to_char?(?(sysdate)+?1/24/60/10???,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line(?to_char?(??((sysdate)+?10?/?(?24*60*60?)???)??,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line(?to_char?(??trunc((sysdate)+?10?/?(?24*60*60?)???)??,'yyyy-mm-dd?hh24:mi:ss')?)?;

end;

/

begin

dbms_output.put_line('當(dāng)前時(shí)間?')?;

dbms_output.put_line(?to_char?(?(sysdate)????,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前時(shí)間??+?1??s??')?;

dbms_output.put_line(?to_char?(?(sysdate)+?(((1/24)/60)/60???)???,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前時(shí)間??+?1??s??')?;

dbms_output.put_line(?to_char?(?(sysdate)+?(((5/24)/60)/60???)???,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前時(shí)間??+?10s??')?;

dbms_output.put_line(?to_char?(??((sysdate)+??(?10?/?(?24*60*60?))????)??,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前?日???')?;

dbms_output.put_line(?to_char?(??trunc((sysdate))??,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前??第2天?1點(diǎn)??')?;

dbms_output.put_line(?to_char?(??trunc(sysdate)+(??1?+??1/24???)?,'yyyy-mm-dd?hh24:mi:ss')?)?;

dbms_output.put_line('當(dāng)前??第2天?9點(diǎn)??')?;

dbms_output.put_line(?to_char?(??trunc(sysdate)+(??1?+??9/24???)?,'yyyy-mm-dd?hh24:mi:ss')?)?;

end;

/