Русский    English

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,

  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:

  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:


Just here we receive the error:

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

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:

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

num   mn1    mn2
1.2    1,20    1,20

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.

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

int_num    int_mon
1    2

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

Bookmark and Share
Pages 1 2 3
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100