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.

money to decimal sql server SQL Query To Check Money Data Type and Convert it to Decimal Dynamically

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 !!!

Comments:  1

  • Sam Khan

    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.