Use of the Oracle trunc () function

Use of the Oracle trunc () function

1.TRUNC (for dates)

The date value that the Trunc function truncates for the specified element.

Its specific syntax format is as follows:

TRUNC (Date[,fmt])

which

Date a datetime value

FMT date format, which is truncated by the specified element format. Ignoring it is truncated by the nearest date.

Here's how this function is used:

TRUNC (To_date (' 24-nov-1999-5:00pm '), ' 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 ')--return to 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 ')--return to current month 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 make the corresponding rounding selection processing of the specified fractional number before or after it, and it is truncated altogether.

Its specific syntax format is as follows

TRUNC (Number[,decimals])

which

Number to be intercepted and processed

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

Here's how this function is used:

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 portion of the digits to the left of the decimal point is truncated to 0. Similar to rounding, such as a parameter of 1 is rounded to a very bit, if 1, is rounded to 10 bits, and so on.

The use of the--oracle trunc () function
/************** Date ********************/
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 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 return current month day
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 current week
7.select trunc (sysdate, ' hh ') from dual--2011-3-18 14:00:00 current time is 14:41
8.select trunc (sysdate, ' mi ') from dual--2011-3-18 14:41:00 trunc () function no seconds accurate
/*************** Digital ********************/
/*
TRUNC (Number,num_digits)
Number requires a truncated rounding.
The num_digits is used to specify the number of rounding precision. The default value for Num_digits is 0.
TRUNC () function is not rounded when truncated
*/
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

Use of the Oracle trunc () function