April 5th, 2012

SQL Query To Check Money Data Type and Convert it to Decimal Dynamically

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+',','') + 
when 'money' then 'Cast(' + COLUMN_NAME  + ' as decimal(19,4)) Amt' 

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

  • 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.