c# - SQL is rounding my decimal on cast -

i'm using sql server 2005. datatype varchar. i'm trying convert numbers like

1250000 

to

1.25 

within sql query , drop trailing zeroes. have tried number of things no success - have run 'trim not function', etc.

here's have, in each iteration have attempted.

select top 30      var1, var2, var3, var4,      convert(decimal(6,2), (var5 / 1000000)) 'number'       databasetable       var1 = x   select top 30      var1, var2, var3, var4,      cast((var5 / 1000000) decimal(6,2)) 'number'       databasetable       var1 = x  

both queries above rounding nearest million, i.e. 1250000 becomes 1.00, etc. ideas appreciated. changing decimal numeric did not either.

@marc_s absolutely right - stop storing numbers strings!

that said, you're victim of integer math. try:

select convert(decimal(10,2), (var5 / 1000000.0))  

since stored numbers strings, sql server may try perform calculation non-numeric data before applying filter, can say:

select convert(decimal(10,2), (case when isnumeric(var5) = 1 var5 end / 1000000.0)) 

[note isn't perfect either.]

if doesn't work you've got bad data. if following does work yields many decimal places:

select      var1, ...,     convert(decimal(10,2), var5) / 1000000 [number]  

then try wrapping convert:

select      var1, ...,     convert(decimal(10,2), convert(decimal(10,2), var5) / 1000000) [number] 

that said, why can't format number in presentation layer?

also, please don't use 'single quotes' column aliases ... this syntax deprecated in forms, , incorrectly makes column alias string (imho). use [square brackets] or avoid using keywords aliases.