Formating/Round Numbers to the Millions

[JVZ]

I have a requirement to format/round a number in a query to be in the millions. For Example:

Original Value New Value
2,939,101 3
120,918,001 121

Does anybody have any ideas how I would accomplish something like this?

Thanks in advance for your help!

 

[nigelrivett]

select round(val/1000000.0,0)

Depending on the datatype of val you may need to do some convertion.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

Well the best way would be to put this in a Userdefined function.

I would Lop off the last 5 places before the decimal point and anything after the decimal point if you have one.(Use Left, Len functions to get this accomplished, if decimals it gets more complicated, you probably need the charindex and right functions too in oder to get exactly what you need.) Then I would multiply by .1 to get a decimal in it and then use the round function.