Oracle中TRUNC() 函數用法，oracletrunc

Oracle中TRUNC() 函數用法，oracletrunc

Oracle中TRUNC() 函數用法

1. 用來處理日期

TRUNC(date[, fmt]): 根據fmt格式來截取日期

SELECT TRUNC(SYSDATE,'D')       FROM DUAL;      --本周第一天日期SELECT TRUNC(SYSDATE, 'MM')     FROM DUAL;      --本月第一天日期SELECT TRUNC(SYSDATE, 'Q')      FROM DUAL;      --本季度第一天日期SELECT TRUNC(SYSDATE,'Y')       FROM DUAL;       --本年度第一天日期

2. 用來處理數值

TRUNC(n1, n2): 用來根據n2截取n1並返回結果，n2可省略。截取數值是不進行四捨五入。

SELECT TRUNC(98765.56789) FROM DUAL;--n2省略，預設為0,查詢結果為: 98765SELECT TRUNC(98765.56789,2) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，預設為0,查詢結果為: </span><span style="font-family: Arial, Helvetica, sans-serif;">98765.56</span>SELECT TRUNC(98765.56789,1) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，預設為0,查詢結果為: </span>98765.5SELECT TRUNC(98765.56789,0) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，預設為0,查詢結果為: </span>98765SELECT TRUNC(98765.56789,-1) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，預設為0,查詢結果為: </span>98760SELECT TRUNC(98765.56789,-2) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，預設為0,查詢結果為: </span>98700

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

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：david.louis.tian@outlook.com

著作權＠：轉載請標明出處！
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()函數沒有秒的精確
 
oracle trunc()函數對於日期與時間，需要詳細解答與舉例？

Trunc Function (with dates)

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.
oracle中，trunc函數返回一個按照特定計量單位截取後的date值
The syntax for the trunc function is:
文法如下：
trunc ( dat1e, [ format ] )

da1te is the date to truncate.
da1te是要截斷的date
format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
format 是截取時依據的計量單位(類似於數字中的精度)。如果format省略，date1就返回當天的日期值,即只保留日期，時間為 0:00:00

Below are the valid format parameters:
以下是合法的參數值：
UnitValid 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'...餘下全文>>