Sometimes we need to convert one data type to another data type to map columns in sql server. You might not be allowed to change table structure directly. In this case you’ve to convert in your query or stored procedure. Suppose you’ve to convert money data-type to decimal for greater precision in calculation then you can do it easily using Cast or Convert methods.
Suppose you don’t know which column is money type and have to convert all money type to decimal for a table then you can do it with dynamic SQL.
Declare @sql varchar(1000) SELECT @sql = Coalesce(@sql+',','') + Case DATA_TYPE when 'money' then 'Cast(' + COLUMN_NAME + ' as decimal(19,4)) Amt' else COLUMN_NAME end from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Orders' Exec ('select '+ @sql + ' from Orders')
In above SQL, Column name is retrieved from information schema, Cast syntax is used for money data type columns and stored in @sql variable which is a part of dynamic select query.
If you know column name and want to check whether it is money type or not then use SQL_VARIANT_PROPERTY
select OrderID, CustomerID, OrderDate, (case SQL_VARIANT_PROPERTY(Amount ,'BaseType') when 'money' then Cast(Amount as decimal(10,2)) else 100.00 end) Amt from Orders
Enjoy SQLing !!!
Great Article :)
We can also submit our .net related links on http://www.dotnettechy.com to improve traffic.
The dotnettechy.com is a community of .Net developers joined together to learn, to teach, to find solutions, to find interview questions and answers, to find .net website / blog collection and to have fun programming.