Detailed explanation of Oracle's common function Trunc

This article mainly introduces the common Oracle function Trunc and the explanation of the usage of Trunc function. Friends in need can refer to it. I hope it can help everyone.

Recommended related mysql video tutorials: "mysql tutorial"

1. Trunc(date)

trunc intercepts date type data with the specified element

Syntax: trunc (date, [format])

date– Date format value

format – Date formats such as 'mm', 'yyyy', etc. will intercept the date from the specified date format

For example:

trunc(sysdate,'yyyy')=‘01-01月-17'(sysdate='21-11月-17'); --返回今年的第一天
trunc(sysdate,'mm')=‘01-11月-17'; --返回本月第一天
trunc(sysdate,'d')=‘19-11月-17'; --（今天星期二，一周从周日开始计算）返回本周第一天

2 Trunc(number,[number1])

trunc(number) is similar to round(), except that the first digit that is truncated is not rounded

For example:

round(89.125,2)=89.13
trunc(89.125,2)=89.12
trunc(89.125,-1)=80

number1 The default value is 0.

The following is an introduction to the usage of the Oracle trunc() function

1.TRUNC (for dates)

　The date value truncated by the TRUNC function for the specified element.

The specific syntax format is as follows:

TRUNC（date[,fmt])

Among them:

Date is a date Value

　fmt Date format, the date will be truncated by the specified element format. Ignore it and truncate from the nearest date

　The following is the usage of this function:

　TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon -yyyy hh:mi am')

　='24-Nov-1999 12:00:00 am'

　TRUNC(TO_DATE('24-Nov-1999 08:37 pm', 'dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'

　trunc(sysdate,'yyyy') --Returns the current year's number one day.

trunc(sysdate,'mm') --Returns the first day of the month.

trunc(sysdate,'d') --Returns the first day of the current week.

trunc(sysdate,'dd')--returns the current year, month and day

2.TRUNC (for number)

The TRUNC function returns the processed value and its work The mechanism is very similar to the ROUND function, except that this function does not round the parts before or after the specified decimal, but truncates them all.

The specific syntax format is as follows

TRUNC（number[,decimals]）

Among them:

Number is the value to be intercepted

　decimals specifies the number of digits to be retained after the decimal point. Optional, ignore it and truncate all decimal parts

The following is the usage of this function:

TRUNC (89.985, 2) = 89.98

TRUNC (89.985) =89

　TRUNC（89.985，-1）=80

Note: The second parameter can be a negative number, which means that the part after the specified number of digits to the left of the decimal point is truncated, that is, all are 0 remember. Similar to rounding, for example, if the parameter is 1, it is rounded to tenths, if it is -1, it is rounded to tenths, and so on.

--Usage of Oracle trunc() function

##

/**************日期********************/
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

I believe everyone already knows about Oracle’s commonly used function Trunc , feel free to try it yourself.

Related recommendations:

Detailed explanation of Oracle’s custom split function example

A brief analysis of the application of table functions in Oracle

Oracle minus usage details and application examples

The above is the detailed content of Detailed explanation of Oracle's common function Trunc. For more information, please follow other related articles on the PHP Chinese website!