CODEWITHSUNDEEP
sqlsql-servercalculated-columns
ScottG
ScottG

Reputation: 11119

Create a computed column and round

I have 3 numeric(18,2) columns in my table. I want to create a fourth column that is computed. I created the computed column in SSMS as numeric (18,2) just like the other 3 columns.

When I run a SELECT against my new column, it computes it fine, but it's not rounded to two decimal places. I've tried using the ROUND function inside my computed column definition but that didn't work. It reset my data type to numeric (38,6) and now won't let me change it.

My new column definition is ((Length * Width * Height) / 166)

I want the end result rounded to two decimal places. What am I doing wrong?

Upvotes: 4

Views: 2005

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

You need to convert the computed column to numeric(18,2)

Example :

CREATE TABLE computed
  (
     a NUMERIC(18, 2),
     b NUMERIC(18, 2),
     c AS CONVERT(NUMERIC(18, 2), a * b)
  )

INSERT INTO computed VALUES (1,2)

SELECT *
FROM   computed

Upvotes: 3

Related Questions