How to Convert Integer to Decimal in SQL Server Query

How to convert integer to decimal in SQL Server query?

SELECT height/10.0 AS HeightDecimal FROM dbo.whatever;

If you want a specific precision scale, then say so:

SELECT CONVERT(DECIMAL(16,4), height/10.0) AS HeightDecimal
  FROM dbo.whatever;

how to convert int to decimal in SQL

When you specify it as DECIMAL(12,9) this means your number can have up to 12 digits (excluding . ) and out of that 9 digits will be after decimal part. Which means the maximum value that you can store here is 999.999999999.

in your sample scenario above, the number has got 15 integer parts which are too high than the scope of your variable, as you can do either of the following

1. Change the size of variable from DECIMAL(12,9) to higher integer precision like DECIMAL(25,9)
2. Add a validation on your application to restrict the number of characters that the user can enter

SQL Server converting integers to decimal

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.

How to change data type of a column in an SQL table from integer to decimal

Easy - just run this SQL statement

ALTER TABLE dbo.Budget
ALTER COLUMN ROE DECIMAL(20,2)   -- or whatever precision and scale you need.....

See the freely available MSDN documentation on what exactly the precision, scale and length in decimal numbers are and what ranges of values are possible

SQL - Convert number to decimal

You need something like that:

SELECT CONVERT(DECIMAL(15,2),12345/100.0) 

SQL Cast from int to decimal

Write it like this:

SELECT CAST(CAST(Total As float) / TotalAnswers * 100 As decimal(8, 2))

Converting Varchar Value to Integer/Decimal Value in SQL Server

Table structure...very basic:

create table tabla(ID int, Stuff varchar (50));

insert into tabla values(1, '32.43');
insert into tabla values(2, '43.33');
insert into tabla values(3, '23.22');

Query:

SELECT SUM(cast(Stuff as decimal(4,2))) as result FROM tabla

Or, try this:

SELECT SUM(cast(isnull(Stuff,0) as decimal(12,2))) as result FROM tabla

Working on SQLServer 2008

---

Related Topics