SQL Server 2000 Truncate Decimal Places
Posted on 2008-06-23
Hi! I created the following code in a stored procedure in SQL Server 2000. It works okay if the values I'm converting don't go above the precision and scale for the field. If there are more decimal places than indicated in the scale, I get the error:
"Server: Msg 8115, Level 16, State 6, Procedure spMoveData, Line 14
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
Here's the insert statement which starts on line 14:
Insert DataTable(SID,SDate, STime, A,CC, TimeInHours, V, L, Tester)
Select SID, SDate, STime,
CAST(cast(Value1 as float) AS DECIMAL(9, 5)),
CAST(cast(Value2 as float) AS DECIMAL(10, 6)),
CAST(cast(Value3 as float) AS DECIMAL(10, 5)),
CAST(cast(Value4 as float) AS DECIMAL(7, 3)),
CAST(cast(Value5 as float) AS DECIMAL(10,5)),
Is there way to just truncate the decimal places if there are more decimal places in the number being loaded than the field is set up? I don't need any more dec places to the right than what is indicated in the cast statement. For example, if value4, which can be (7,3) has 4 decimal places, I just want to truncate the 4th decimal place to make it 3 decimal places.
Thanks for your help.