SQL data manipulation language

SELECT statement

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,

Console

Execute

SELECT CAST(1234.6 AS VARCHAR(5))

SELECT CAST(1234.6 AS VARCHAR(5))

    Scheme

No scheme

We would expect that we'll receive the error message. Correctly, here is this message:

It is natural that we shall expect the same message when running the following operator:

Console

Execute

SELECT CAST(123456 AS VARCHAR(5))

SELECT CAST(123456 AS VARCHAR(5))

    Scheme

No scheme

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:

Console

Execute

SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT)

SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT)

    Scheme

No scheme

Just here we receive the error:

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:

Console

Execute

SELECT 1.2 num, $1.2 mn1, CAST(1.2 AS MONEY) mn2;

select 1.2 num, select 1.2 num, $1.2 mn1, cast(1.2 as MONEY) mn2;.2 mn1, cast(1.2 as MONEY) mn2;

    Scheme

No scheme

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.

Console

Execute

SELECT CAST(1.75 AS INT) int_num, CAST($1.75 AS INT) int_mon;

select cast(1.75 as INT) int_num, cast(select cast(1.75 as INT) int_num, cast($1.75 as INT) int_mon;.75 as INT) int_mon;

    Scheme

No scheme

It is money, it is impossible to lose them so easily!