Русский    English

About implicit conversion of SQL Server data types page 2

So, really we have that values of one data type can be compared. For transformation of types the standard offers function CAST. I.e. generally we should transform compared values to one type, and then to carry out operation of comparison (or assignments). What will take place, if we of a variable (or column) one type shall simply to assign value of other data type? We shall consider a simple example of a code on  T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL (SQL Server 2000 is used)

  1. DECLARE @vc VARCHAR(10), @mn MONEY, @ft FLOAT
  2. SELECT @vc = '499.99'
  3. PRINT @vc
  4. SELECT @ft = @vc
  5. PRINT @ft

Here we describes three variables accordingly string type (VARCHAR), monetary type (MONEY) and numbers with a floating point (  Тип данных с плавающей точкой. float). Further a string variable we assign a constant of corresponding type, and then we assign a variable such as FLOAT value of a string variable. In result we receive two identical results - 499.99 (PRINT operator carries out a conclusion to the console). That has taken place, refers to as implicit transformation data types, i.e. string value - '499.99' has been automatically resulted in data type FLOAT and assigned by a variable @ft.

Let's add in the end of a code still a pair of strings:

  1. SELECT @mn = @vc
  2. PRINT @mn

As a result we shall receive two similar messages about error:

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.


Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this query.

In one of which that implicit transformation to money type is not supposed is underlined, and in other - about inadmissibility and the opposite (to varchar) transformations. As one would expect, we are offered to use evident transformation from the help of function COVERT. However to be closer to the standard, we shall take advantage of function CAST:

  1. SELECT @mn = CAST(@vc AS MONEY)
  2. PRINT @mn

We got rid of the first message about error. The conclusion that the second message is given by operator PRINT arises. Instincts prompt to glance in the help. In  Электронная документация SQL Server Books OnlineBOL the variable which can be used in this operator is spoken about operator PRINT that should be any allowable string type (char or varchar) or should be resulted in this type implicitly. Let's rewrite last line so


All works. The main conclusion which we from here take consists what not all types (even if we do not see the special reasons for that) suppose implicit conversion. In particular, implicit reduction such as MONEY to types CHAR VARCHAR, NCHAR, NVARCHAR and on the contrary is not carried out.

And now about the reason, which has forced me to write very many words. On my untidiness appeared that in the basic and verifying base some equivalent columns had different data type. For example, the field price in РС table had FLOAT data type in one database and MONEY data type - in another one. During very long time it did not influence in any way work of the site, but here suddenly for the some days our two participants have decided to use implicit transformation of types to their queries with known result.:-)

I have decided to not be limited to apologies, and to write an opus about features of realization, hoping that it will bring more advantage than my apologies.

As to data types, the noticed divergence is already harmonized. Also scripts for uploading are updated.

Unfortunately, the examples resulted here cannot be executed directly on a site. It is caused by that while we did not realize an opportunity of performance of sets of operators (packages).

Therefore that who will want to check up validity told, it is necessary to take advantage Management Studio.

Bookmark and Share
Pages 1 2
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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions 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.