How to use oracle trunc () function, oracletrunc

How to use oracle trunc () function, oracletrunc

Oracle trunc () function is one of the most common functions. The following describes the usage of oracle trunc () function for your reference () functions have a deeper understanding.

1. TRUNC (for dates)

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

The syntax format is as follows:
TRUNC (date [, fmt])

Where:
Date: A date value.
Fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest date.

The usage of this function is as follows:

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

If no format is specified for round (date, 'format'), if the time in the date is before noon, the time in the date is truncated to 12. m. (midnight, the beginning of a day), or enter the next day.

If format is not specified for TRUNC (date, 'format'), the date is cut to 12 A. M., regardless of whether the condition is before noon.

2. TRUNC (for number)

The TRUNC function returns the processed value. Its working mechanism is very similar to that of the ROUND function, except that the function does not ROUND or select the part before or after the specified decimal number.

The syntax format is as follows:
TRUNC (number [, decimals])

Where:
Number the value to be intercepted
Decimals indicates the number of digits after the decimal point to be retained. Optional. If this parameter is ignored, all decimal parts are truncated.

The usage of this function is as follows:
TRUNC (89.985, 2) = 89.98
TRUNC (89.985) = 89
TRUNC (89.985,-1) = 80

Note: The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated.

The usage of the oracle trunc () function is described above.

Example of Oracle trunc () function:

 

-- Oracle trunc () function usage ********************/ 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 return the first day of the current year 4. select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day 5. select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year 6. select trunc (sysdate, 'D') from dual -- 2011-3-1 3 (Sunday) returns the first day of the current week 7. select trunc (sysdate, 'hh') from dual -- 14:00:00 the current time is. select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 TRUNC () the function does not have second precision ****************** ** // * TRUNC (number, num_digits) Number. Num_digits is used to specify the number to take an integer. The default value of Num_digits is 0. When TRUNC () function is intercepted, No rounding is performed */9. select trunc (123.458) from dual -- 12310. select trunc (123.458, 0) from dual -- 12311. select trunc (123.458, 1) from dual -- 123.412.select trunc (123.458,-1) from dual -- 12013. select trunc (123.458,-4) from dual -- 014. select trunc (123.458, 4) from dual -- 123.45815.select trunc (123) from dual -- 12316. select trunc (12317) from dual. select trunc (123,-1) from dual -- 120

Oracle trunc () function usage

1. select trunc (sysdate) from dual -- 2011-3-18 today's date is 2011-3-18
2. select trunc (sysdate, 'mm') from dual -- 2011-3-1 returns the first day of the current month.
3. select trunc (sysdate, 'yy') from dual -- 2011-1-1 return the first day of the current year
4. select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day
5. select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year
6. select trunc (sysdate, 'D') from dual -- 2011-3-13 (Sunday) returns the first day of the current week
7. select trunc (sysdate, 'hh') from dual -- 2011-3-18 14:00:00 current time is
8. select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 the TRUNC () function has no precision in seconds.

Oracle Trunc function instruction

If y is a negative number, x is truncated at the | y | bit on the left of the decimal point, for example, TRUNC (5.75,-1) = 0 5.75 is regarded as 0005.75.000 and truncated from the first digit on the left of the decimal point to the left, isn't it 0?

The second problem, for example: ROUND (5.75) = 6, indicates to integer 5.75 at 0 decimal places, where 0 decimal places is 7? 0 decimal places are none, not 7, and 7 is the first decimal places.
The sum of 5.75 is 6. It is rounded to five. Otherwise, there will be no difference between round and trunc.

ROUND (5.75, 1) = 5.8, where is the decimal number of 1st digits 5? No. The first decimal point is 7 rounded to 5.8.

ROUND (5.75,-1) = 10, for 5.75 on the left |-1 | bitwise, the first on the left is ten? The first digit on the left of the decimal point is a single digit, which is also regarded as 00005.75000. It is taken from the left of a single digit and rounded down. The ten digits on the left of a single digit are 0. Because 5 is in the first place, it is 10.