<div>
or even<br />
<br />
UPDATE StockHist <br />
SET ClosePrice = CAST(ROUND(.33333 * ClosePrice,2) as decimal (8,2)) 
</div>


<div>
There's not much point to converting it to a decimal if you've already used the ROUND function.
</div>


<div>
except that he wants 2 decimal places and not 4.<br />
<br />
11.56 vs. 11.5600 - yes it is the same amount mathmatically but not visually.
</div>


<div>
It doesn't matter how you update it. &nbsp;If the datatype is smallmoney, it will return as smallmoney, not decimal. &nbsp;Check the code below. &nbsp;There is no difference.<br />

<pre><code class="code-1 nolinks" id="code-262526"><span>CREATE TABLE #a (X INT IDENTITY(1,1), Y SMALLMONEY)</span>
<span> </span>
<span>INSERT INTO #a VALUES (ROUND(11.5585,2))</span>
<span>INSERT INTO #a VALUES (CAST(11.5585 AS DECIMAL(8,2)))</span>
<span>INSERT INTO #a VALUES (ROUND(CAST(11.5585 AS DECIMAL(8,2)),2))</span>
<span> </span>
<span>SELECT * FROM #a</span>
<span> </span>
<span>DROP TABLE #a</span>
</code></pre>
</div>


<div>
I changed the data type to decimal(8,2) as suggested by aaronakin. I appreciate the CAST method described by J Carter that I can use for reports when the datatype is smallmoney.<br />
<br />
The main objective here was to store the result rounded to 2 decimal places.<br />
<br />
Thanks to both for the detailed discussion.
</div>


<div>
<div class="content wysiwyg-content">
I need to multiply a smallmoney value and store the result rounded to 2 decimal places.<br />
<br />
Example:<br />
Column Data type is smallmoney<br />
<br />
Initial value = 34.68<br />
<br />
UPDATE StockHist <br />
SET ClosePrice = .33333 * ClosePrice <br />
<br />
Returns 11.5585<br />
<br />
Need to return 11.56 (store rounded to only 2 decimal places).
</div>
</div>


I need to multiply a smallmoney value and store the result rounded to 2 decimal places.

Example:
Column Data type is smallmoney

Initial value = 34.68

UPDATE StockHist 
SET ClosePrice = .33333 * ClosePrice 

Returns 11.5585

Need to return 11.56 (store rounded to only 2 decimal places).

I need to multiply a smallmoney value and store the result rounded to 2 decimal places.

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.