Explain the usage of common Oracle functions Trunc and Trunc, oracletrunc

Explain the usage of common Oracle functions Trunc and Trunc, oracletrunc

1. Trunc (date)

Trunc intercepts data of the date type with the specified Element

Syntax: trunc (date, [format])

Date-date format Value

Format-the date format is 'mm' or 'yyyy '.

For example:

Trunc (sysdate, 'yyyy') = '01-August 17-17' (sysdate = '21-August 17-17'); -- returns the first day of this year trunc (sysdate, 'mm') = '01-August 17-17'; -- returns the first day of the month, trunc (sysdate, 'D') = '19-August 17-17'; -- (today, Tuesday, returns the first day of the week.

2 Trunc (number, [number1])

Trunc (number) is similar to round (), but does not round the first number to be truncated.

For example:

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

The default value of number1 is 0.

The following describes the usage of Oracle trunc () functions.

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'

Trunc (sysdate, 'yyyy') -- returns the first day of the current year.

Trunc (sysdate, 'mm') -- returns the first day of the current 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. 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. It is similar to integer. For example, if the parameter is 1, the value is rounded to the nearest bit. If the parameter is-1, the value is rounded to ten digits, and so on.

-- 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-13 (Sunday) return the first day of the current week 7.se Lect 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

Summary

The preceding section describes the usage of common Oracle functions Trunc and Trunc. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!