Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to use the Trunc function in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Trunc (date)

Trunc intercepts data of date type with the specified element

Syntax: trunc (date, [format])

Value in date- date format

Format- date formats such as' mm','yyyy', etc., intercept date from a specified date format

For example:

Trunc (sysdate,'yyyy') ='01-01 January-17'(sysdate='21-November-17');-- return to the first day of the year trunc (sysdate,'mm') ='01-November-17 days;-- return to the first day of the month trunc (sysdate,'d') ='19-November-17 days;-- (today Tuesday, the week starts on Sunday) return to the first day of the week

2 Trunc (number, [number1])

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

Such as:

Round (89.125) = 89.13trunc (89.125) = 89.12trunc (89.125) = 80

The default value for number1 is 0.

The following is the use 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])

Where:

Date one date value

Fmt date format, which will be truncated by the specified element format. Ignore it and it is truncated by the most recent date

The following is the use 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 first day of the year.

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 working mechanism is very similar to the ROUND function, except that the function does not round off the parts before or after the specified decimal number, but truncates them all.

The specific syntax format is as follows

TRUNC (number [, decimals])

Where:

The value of number to be intercepted

Decimals indicates that the number of digits after the decimal point should be retained. Optional, ignore it and truncate all decimal parts

The following is the use of this function:

TRUNC (89.985) = 89.98

TRUNC (89.985) = 89

TRUNC (89.985) = 80

Note: the second parameter can be negative, which is truncated after the specified number of places to the left of the decimal point, that is, it is marked with 0. Similar to rounding, for example, if the parameter is 1, it is rounded to ten, if it is-1, it is rounded to ten, and so on.

-- usage of the Oracle trunc () function

/ * date * / 1.select trunc (sysdate) from dual-- 2011-3-18 Today's date is 2011-3-182.select trunc (sysdate,' mm') from dual-- 2011-3-1 returns the first day of the month. 3.select trunc (sysdate,'yy') from dual-- 2011-1-1 returns the first day of the year 4.select trunc (sysdate) 'dd') from dual-- 2011-3-18 returns the current day of the year 5.select trunc (sysdate,'yyyy') from dual-- 2011-1-1 returns the first day of the year 6.select trunc (sysdate,'d') from dual-- 2011-3-13 (Sunday) returns the first day of the week 7.select trunc (sysdate,' hh') from dual-2011-3-18 14:00:00 the current time is 14:41 8.select trunc (sysdate 'mi') from dual-- 2011-3-18 14:41:00 the TRUNC () function does not have the exact number of seconds / * number * / * TRUNC (number,num_digits) Number needs to be truncated. The number used by Num_digits to specify the rounding precision. The default value for Num_digits is 0. The TRUNC () function is intercepted without rounding * / 9.select trunc (123.458) from dual-- 12310.select trunc (123.458) from dual-- 12311.select trunc (123.458) from dual-- 123.412.select trunc (123.458) from dual-12013.select trunc (123.458) 4) from dual-- 014.select trunc (123.458) from dual-- 123.45815.select trunc (123) from dual-- 12316.select trunc (123) from dual-- 12317.select trunc (123) from dual-- 120 this is how the Trunc function shared by Xiaobian is used in Oracle. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.