jts_2003 (8/17/2010)
So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?
No. The third argument is to truncate instead of rounding. Rounding can be both up and down (when rounding to the integer, 1.7 will be rounded up but 1.3 will be rounded down). But truncation can also have the effect of rounding either up or down - truncating 1.7 will result in 1 (down), but truncating -1.7 will result in -1 (up).
To round up or down, use CEILING() and FLOOR(). To round according to normal rounding rules, use ROUND() with the default function. And to truncate, use ROUND() with the non-default function.
john.moreno (8/17/2010)
Nope. It truncates and then does the rounding. ROUND(5/3.0, 3,2) would have produced a result of 1.67 (up from the nominal value of 1.666666).
The ROUND() function with the non-zero function actually only truncates. The result of ROUND(5/3.0, 3, 2) is 1.666000 - truncated to the third decimal. This can be verified by running SELECT ROUND(5/3.0, 3, 2);
When you assign that result to a variable that is declared to have two decimal places (as in the code used in the QotD), the assignment forces an implicit conversion. Normal rounding rules apply, so the result will then be 1.67. But that is not related to the ROUND() function itself.