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.
The CONVERT function in Transact-SQL (T-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.Transact-SQL behaves similarly.
Conversion of MONEY data type
The monetary data type is not standard. A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server has two monetary types:
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!