Sometimes when you’re working with calculated columns in SQL Server, you might get results in a data type that’s less than ideal. For example, maybe it’s a decimal or float when you really need an integer. This can result in the output not appearing exactly the way you want, such as with a bunch of unnecessary trailing decimal zeros.
In such cases, you’ll probably want to remove these trailing zeros from the result. The CAST() function is perfect for this kind of cleanup. You can use it to convert the value to a more suitable type.
Example
Let’s create and populate a simple table called ProductionMetrics that tracks manufacturing output and efficiency:
CREATE TABLE ProductionMetrics (
LineID INT IDENTITY(1,1) PRIMARY KEY,
UnitsProduced DECIMAL(10,2),
HoursWorked DECIMAL(10,2),
ErrorRate DECIMAL(5,4) -- percentage of defective units
);
INSERT INTO ProductionMetrics (UnitsProduced, HoursWorked, ErrorRate)
VALUES
(1523.75, 41.5, 0.0175),
(980.25, 35.0, 0.0220),
(2450.10, 60.25, 0.0125),
(1899.50, 52.75, 0.0190);Now let’s say we want to estimate a “performance score” for each line by dividing the total units produced by hours worked, rounding it to the nearest whole number, and converting it into an integer for reporting.
If we just divide, we’ll get a decimal data type. The same applies even if we round it:
SELECT
LineID,
UnitsProduced,
HoursWorked,
UnitsProduced / HoursWorked AS RawValue,
ROUND(UnitsProduced / HoursWorked, 0) AS RoundedValue
FROM ProductionMetrics;Result:
LineID UnitsProduced HoursWorked RawValue RoundedValue
----------- ------------- ------------ ------------------------- -------------------------
1 1523.75 41.50 36.7168674698795 37.0000000000000
2 980.25 35.00 28.0071428571428 28.0000000000000
3 2450.10 60.25 40.6655601659751 41.0000000000000
4 1899.50 52.75 36.0094786729857 36.0000000000000
While this provides us with the right data, we might not want all those zeros or decimal places.
If you run this example on your system, you may or may not see all those zeros, depending on your set up. For example, if I run that in my command line tool, the rounded values appear without all the zeros. But when I run it in other tools, the zeros are included.
In any case, the result is returned as decimal, and there’s a good possibility that the query will return zeros, which for the purpose of this article, is not what we want. Here’s how we can fix that:
SELECT
LineID,
UnitsProduced,
HoursWorked,
UnitsProduced / HoursWorked AS RawValue,
CAST(ROUND(UnitsProduced / HoursWorked, 0) AS INT) AS PerformanceScore
FROM ProductionMetrics;Result:
LineID UnitsProduced HoursWorked RawValue PerformanceScore
----------- ------------- ------------ ------------------------- ----------------
1 1523.75 41.50 36.7168674698795 37
2 980.25 35.00 28.0071428571428 28
3 2450.10 60.25 40.6655601659751 41
4 1899.50 52.75 36.0094786729857 36
Here we use CAST() to cast the output of ROUND() as an integer. This resulted in all the zeros being removed.
The ROUND() function doesn’t automatically change the data type from decimal to integer. If we pass a decimal value it returns a decimal value. So even if we round to 0 decimals, SQL Server still treats the result as a decimal value, not an integer.
For example, ROUND(36.71, 0) returns 37.00 (a decimal), not 37 (an integer). If you try to use that result in a context where only integers make sense (like indexing, reporting totals, or joining to a table that stores integer IDs) it can cause type mismatches or unexpected results.
By wrapping the expression in CAST(... AS INT), we make sure SQL Server explicitly converts the numeric result into an integer. In this query, the final PerformanceScore column is a clean integer value that’s easier to use for performance summaries or dashboards.