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,查询结果为: 98765
SELECT 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.5
SELECT TRUNC(98765.56789,0) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，默认为0,查询结果为: </span>98765
SELECT TRUNC(98765.56789,-1) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，默认为0,查询结果为: </span>98760
SELECT TRUNC(98765.56789,-2) FROM DUAL;--<span style="font-family: Arial, Helvetica, sans-serif;">n2省略，默认为0,查询结果为: </span>98700

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

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

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 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'...余下全文>>