Oracle trunc () function usage

This article introduces the usage of the Oracle trunc () function in the format of the TRUNC function. I have some experience now. When the format is day, it is only accurate to the day, regardless of the number of months in a few years, it is enough to make sure that a certain day of the year is required to use trunc (date, \ '\ 'dd \'\').

The format is year, accurate to ----- year.

For months, accurate to ------ year, month (no matter which year, as long as it is the same month and day)
Is the day, accurate to ------ year, month, day (regardless of which month, only care about the day)

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:

The Code is as follows:	Copy code
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'

When 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., and the condition before noon is not considered.

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.
 
-- Oracle trunc () function usage
********************/

The Code is as follows:

Copy code

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. ********************/ /* 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 -- 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) from dual -- 17. select trunc (123,-1) from dual -- 120 Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; -- display the current time 2011-12-29 16:24:34 Select trunc (sysdate, 'Year') from dual; -- truncated to the year (the first day of this year) 2011-1-1 Select trunc (sysdate, 'q') from dual; -- truncated to the quarter (the first day of the quarter) 2011-10-1 Select trunc (sysdate, 'month') from dual; -- intercept to month (the first day of this month) 2011-12-1 Select trunc (sysdate, '') from dual; Null Select to_char (trunc (sysdate), 'yyyymmdd hh24: mi: ss') from dual; -- intercepted to the day by default (00:00:00 Of The Day) 00:00:00 20111229 Select trunc (sysdate-1, 'w') from dual; -- Thursday closest to the current time, returns the day if it is Thursday; Otherwise returns the last Thursday 2011-12-22 Select trunc (sysdate, 'ww ') from dual; -- intercepted last weekend (Saturday last week) 2011-12-24 Select trunc (sysdate, 'day') from dual; -- intercept to week (the first day of the week, that is, last Sunday) 2011-12-25 Select trunc (sysdate, 'iw') from dual; -- 2nd days of the week, that is, this Monday 2011-12-26 Select to_char (trunc (sysdate, 'dd'), 'yyyymmdd hh24: mi: ss') from dual; -- truncated to the day (00:00:00 of the current day) 00:00:00 20111229 Select trunc (sysdate, 'hh24') from dual; -- truncated to the hour (current hour, 0 minutes 0 seconds) 2011-12-29 16:00:00 Select trunc (sysdate, 'mi') from dual; -- truncated to minute (current minute, zero second) 2011-12-29 16:24:00 Select trunc (sysdate, 'ss') from dual; -- an error is reported, which is not accurate to the second format.