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

This article will explain in detail what the function Trunc commonly used in Oracle means. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge 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 that's what the common Oracle function Trunc means. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.