CODEWITHSUNDEEP
sql-server
Hard Tacos
Hard Tacos

Reputation: 370

SQL Server converting integers to decimal

I looked at this post: How to convert integer to decimal in SQL Server query? but it is not doing as I had expected.

I have a simple equation - ( [X] - [Y] ) / [X] where X and Y are arbitrary int values from columns. I would like to store the result as a decimal including 1 (or 100%). But when I try to query this, I don't seem to be getting what I expect. If I use the query below - I get 0.

Here is my query so far:

CONVERT(DECIMAL(3, 2), (s4.Duration - s2.Interruption_Min_Total) / s4.Duration) AS Percentage

If you need to see more of my actual query, please ask - I will post it if need be.

Any help on this would be greatly appreciated.

Upvotes: 1

Views: 242

Answers (1)

Hannah Vernon
Hannah Vernon

Reputation: 3472

You need to force SQL Server to convert int used in the division into a decimal first, then SQL Server will return a decimal:

DECLARE @v1 INT;
DECLARE @v2 INT;
DECLARE @v3 INT;

SET @v1 = 5;
SET @v2 = 2;
SET @v3 = 4;

SELECT (@v1 - @v2) / @v3;

SELECT (@v1 - @v2) / CONVERT(DECIMAL(10,2), @v3);

Please take a look at the answers on this question.

Upvotes: 2

Related Questions