How Do I Write Sql Statemen That Can Cast String To Double

I assume you're using MS-SQL in which case you could try rounding the column first before the cast as maybe the column is stored in a decimal format

select cast(round(amount_paid, 0) as int) as "AmountPaid"
  from contractpayment

Alternatively, if your intention was to Cast as double then you should use the Double qualifier in the Cast() function [i.e. Cast(<Column Name> as Double)]


I don't know if it's relevant but Oracle has a built function for converting varchar2 to a number as follows:

to_number('1210.73', '9999.99') would return the number 1210.73
to_number('546', '999') would return the number 546
to_number('23', '99') would return the number 23

So your example would be written as follows:

select to_number(amount_paid '9,999,999') as "AmountPaid"
  from contractpayment

It could be that you are not using one of the correct type identifiers in your Cast() function. From what I can see in the documentation, "int" is not one of the defined types

I got this from the MySQL Developer site http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html:


I guess you should also ensure that the contents of the column can be safely converted in that it doesn't contain any non numeric characters such as currency indicators, semi colons etc. and also ensure that the column in question is not null.

What is the output if you just do a straightforward select without a Cast() or Convert()?