"Go" Oracle Built-in function trunc use

Original link http://blog.csdn.net/oscar999/article/details/16839417

Preface Trunc is a shorthand for truncate. The Chinese translation is "truncate" or "truncated". On the surface, it should be a string or a number to intercept. There are actually two uses of this function, and the two usages look very different. 1. Use for number types. 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 decimals before or after it, and it is truncated altogether. 2. Use for date types. Use syntax format for number type: TRUNC (Number[,decimals])--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. It is important to note that this parameter can be a negative number, which is expressed as the part that follows the digits to the left of the decimal point to0Replace. such as TRUNC (123.456789,-1) = -usage Examples:1) decimals not set, cut off all the decimal partsSelectTRUNC (123.456789) fromdual; return:1232) decimals is set to a positive integerSelectTRUNC (123.456789,2) fromdual; Return123.45Compare the status of using round:SelectROUND (123.456789,2) fromdual; Return123.46(rounded up)3) decimals is set to a negative integerSelectTRUNC (123.456789,-1) fromdual; return: -(The last one from3Become0up)4) decimals set to decimalSelectTRUNC (123.456789,2.8) fromdual; SelectTRUNC (123.456789,2.4) fromdual; return:123.45It seems that the decimals can be set to decimal, but the fractional part is completely ignored, and setting decimals is meaningless. (however, if this parameter is partially computed, there is no need to do a type conversion) use the syntax format for the date type: TRUNC (date[,fmt]) Date a datetime value FMT date format, which is intercepted by the specified element format. Ignoring it, the use of this function is interpreted by the most recent date. The first day of the date (parametric fmt) Date of the data FMT: the corresponding parameter table: Week Sunday: day or Dy or D (Weekly order: Days, one, two, three, four, five, Vi. date of month: month or Mon or mm or RM date: Q This year: Syear or yyyy or yyy or yy or y (multiple y for precision) Early this century date: CC or SCC "return": Date Use instance: (Take the current date sysdate as an example) 1. Get Current dateSelectTrunc (Sysdate) fromdual; SelectTrunc (Sysdate,'DD') fromdual; return: --November- -2. Return to this week SundaySelectTrunc (Sysdate,'D') fromdual; Because the West is the beginning of the week from Sunday, for China, this return is actually last week's weekend. If you want to get the Chinese habit of this weekend, you need to add7Oh, God. SelectTrunc (Sysdate,'D')+7  fromdual; A similar usage can also be used:SelectTrunc (Sysdate,'Dy')+7  fromdual; SelectTrunc (Sysdate,' Day')+7  fromdual; 3returns the start time of the seasonSelectTrunc (Sysdate,'Q') fromdual; 4. Returns the start time of the monthSelectTrunc (Sysdate,'Month') fromdual; SelectTrunc (Sysdate,'mm') fromdual; 5. Start time of the yearSelectTrunc (Sysdate,' Year') fromdual; SelectTrunc (Sysdate,'yyyy') fromdual; 6. Starting time of the hourSelectTrunc (Sysdate,'HH24') fromdual; Return date of day7. This minute start timeSelectTrunc (Sysdate,'MI') fromdual; Return the same date on the same day other1Note that this function cannot be used with string types:SelectTRUNC ('abcdef',2) fromdual; This usage is wrong. However, this is possible:SelectTRUNC ('123.456789',2) fromdual; The difference, it should not be difficult to understand^^2The dual is frequently used here: Dual is a virtual table that makes up the syntax rules for SELECT, and Oracle guarantees that there will always be only one record in dual. 

[

"Go" Oracle Built-in function trunc use