liuyu000 commented on a change in pull request #1837: [TRAFODION-3306] Add the TRUNC Function in the Trafodion SQL Reference Manual URL: https://github.com/apache/trafodion/pull/1837#discussion_r278786551
########## File path: docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ########## @@ -12460,6 +12460,122 @@ CHAR(20). This expression uses the TRIM function to return the value TRIM(first_name) || ' ' || TRIM (last_name) ``` +[[trunc_function]] +== TRUNC Function + +The TRUNC (TRUNCATE) function truncates `_numeric-expression_` on an optional `_decimal-place_` decimal position(s), that is, it rounds down `_numeric-expression_` to the nearest (or equal) integer closer to zero *without rounding*. + +TRUNC is a Trafodion SQL extension. + +`TRUNC (_numeric-expression_[, _decimal-place_])` + +[[syntax_descriptions_of_trunc]] +=== Syntax Descriptions of TRUNC + +* `_numeric-expression_` + ++ +is an SQL numeric expression to be truncated. + ++ +For more information, see <<numeric_value_expressions,Numeric Value Expressions>>. + +* `_decimal-place_` + ++ +is an integer specifying the precision of the truncation. + +NOTE: `_numeric-expression_` and `_decimal-place_` cannot be NULL. + +NOTE: Currently, `_decimal-place_` can be zero or a positive integer, while *negative integer* is *not supported*. + +NOTE: If `_decimal-place_` is omitted, `_numeric-expression_` is truncated to zero decimal position, which means all digits after the decimal point are removed. + +*Example* + +``` +SELECT TRUNC (-1.2345), TRUNC (1.2345) FROM DUAL + +(EXPR) (EXPR) +----------- ----------- + + -1 1 + +--- 1 row(s) selected. +``` + +NOTE: If `_decimal-place_` is a positive integer, `_numeric-expression_` is truncated to `_decimal-place_` decimal position(s) to the right of the decimal point. + +*Example* + +1 for truncating to a multiple of 0.1 + +2 for truncating to a multiple of 0.01 + +3 for truncating to a multiple of 0.001 + + +*Example* + +``` +SELECT TRUNC (1.2345,1), TRUNC (1.2345,2), TRUNC (1.2345, 3), TRUNC (1.2345, 4) FROM DUAL; + +(EXPR) (EXPR) (EXPR) (EXPR) +------------ ------------ ------------ ------------ + + 1.2 1.23 1.234 1.2345 + +--- 1 row(s) selected. +``` + +NOTE: `_decimal-place_` cannot be larger than the number of decimal digits. + +[[considerations_for_trunc]] +=== Considerations for TRUNC + +* Trafodion provides the <<ceil_function, CEIL Function>>, the <<floor_function, FLOOR Function>>, the <<round_function, ROUND Function>> and the <<trunc_function, TRUNC Function>> to round numbers, these functions can be interchanged in certain circumstances. + ++ +*Example* + ++ +|=== + +^| *Condition* ^| *Result* + +| All of the following conditions for the TRUNC function are true： + + ◦ `_numeric-expression_` is *greater* than zero + + ◦ `_decimal-place_` is not specified + +| *TRUNC* function = *FLOOR* function + +| All of the following conditions for the TRUNC function are true： + + ◦ `_numeric-expression_` is *less* than zero + + ◦ `_decimal-place_` is not specified + +| *TRUNC* function = *CEIL* function + +|=== + +* The TRUNC function is similar to the ROUND function in terms of *reducing the number of decimal places*. + ++ +However, the TRUNC function *does not perform any rounding* as the ROUND function does. Review comment: Thank you, I've incorporated your comment. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
