Data type conversion and CAST function page 3 |
||||||||||||||
There is one feature of use of operator CAST
in SQL Server, which is connected with conversion of a number to its
character representation. What will occur, if the quantity of symbols
in a number exceeds the size of a string? For example, We would expect that we'll receive the error message. Correctly, here is this message: Arithmetic overflow error converting numeric to data type varchar. It is natural that we shall expect the same message when running the following operator: But no. As a result we shall receive a symbol «*» instead of the error message. We do not undertake to judge with what it is connected, however, one day we have faced a problem of diagnostics of a bug in a code in which return transformation to numerical type was carried out afterwards. In our elementary example it will look like: Just here we receive the error: Syntax error converting the varchar value '*' to a column of data type int.
Notes:
The CONVERT function in Conversion of MONEY data typeThe monetary data type is not standard. money: range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 smallmoney: range from -214 748,3648 to 214 748,3647 Scale 4 is for both types. A constant of money data type can be set by means of a prefix $, or by using the transformation of types, for example:
Pay attention to a comma as a separator of "dollars" and "cents" - not a dot! Transformation to the integer data type for numbers and money is not the same: in the first case the fractional part is rejected, in the second, rounding takes place.
It is money, it is impossible to lose them so easily! |