loading..
Русский    English
16:43
go

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,

Console
Execute
  1. SELECT CAST(1234.6 AS VARCHAR(5))

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:

Console
Execute
  1. SELECT CAST(123456 AS VARCHAR(5))

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
  1. SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT)

Just here we receive the error:

Syntax error converting the varchar value '*' to a column of data type int.

Notes:

The CONVERT function in Transact-SQL behaves similarly.

Bookmark and Share
Pages 1 2 3
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100