How do I set Precision and Scale in a calulated column
I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please
Thanks,
Scott
SQL Server Express
How do I set Precision and Scale in a calulated column
I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please
Thanks,
Scott
hi Scott,
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE #t ( d decimal(18,4) DEFAULT 1234.123, D2 AS CONVERT(decimal(8,2), d) ); GO INSERT INTO #t DEFAULT VALUES; SELECT * FROM #t; GO DROP TABLE #t; --<--------- d D2 ------------ -------- 1234.1230 1234.12
regards
Either using a CREATE TABLE or ALTER TABLE statement, and add the precision,scale to the computed column declaration.
Here is an example:
CREATE TABLE #MyTable
( RowID int IDENTITY,
Cost decimal(6,2),
Quantity int,
Total AS cast(( Cost * Quantity ) AS decimal(8,4))
)
INSERT INTO #MyTable VALUES ( 5, 10 )
INSERT INTO #MyTable VALUES ( 2.5, 5 )
INSERT INTO #MyTable VALUES ( 4.55, 5 )
SELECT * FROM #MyTable
DROP TABLE #MyTable
Thank you for helping.
I'm using the Management Studio. Is there a way to accomplish this in the formula line
Thanks again.
Scott
Thanks for helping.
I tried the following:
***********************************
Use SIR
ALTER TABLE dbo.Table_1
ALTER COLUMN Results
nPRECISION(6,2)
Go
************************************
Recieved the following message:
************************************
Msg 4928, Level 16, State 1, Line 2
Cannot alter column 'Results' because it is 'COMPUTED'.
What else might I try
You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula.
Do something like this:
USE SIR;
ALTER TABLE dbo.Table_1
DROP COLUMN Results;
ALTER TABLE dbo.Table_1
ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2)));