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)
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:
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.
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.
Note that in versions of 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 above 2000 implicit conversion of MONEY data type will work.
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
Therefore that who will want to check up validity told, it is necessary to take advantage Management Studio.