Decimal Datatype Scale - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Decimal Datatype Scale

Author

Topic

malik_1700
Starting Member

14 Posts

Posted - 2008-08-13 : 00:11:19

When I write the following code I get the result 3115.01639300 instead of 3115.01639344 . Last two digits in result are 00 instead of 44.I want 3115.01639344 . How it is possible?

declare @v1 decimal(18,8)
declare @v2 decimal(18,8)
declare @v3 decimal(18,8)
declare @v4 decimal(18,8)
declare @v5 decimal(18,8)
declare @v6 decimal(18,8)

set @v1 = 95008.00000000
set @v2 = 40
set @v3 = 30
set @v4 = 366
set @v6 = 100

select @v5= @v1 * @v2 * @v3 / (@v4 * @v6)
select @v5

Result is
3115.01639300

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 04:49:05

Well I don't exactly understand why sqlserver 2000 does this but...

This will provide you with the right answer but I will understand if you don't like it (because I sure as hell don't)


DECLARE @v1 FLOAT
DECLARE @v2 FLOAT
DECLARE @v3 FLOAT
DECLARE @v4 FLOAT
DECLARE @v5 FLOAT
DECLARE @v6 FLOAT


SET @v1 = 95008.00000000
SET @v2 = 40.00000000
SET @v3 = 30.00000000
SET @v4 = 366.00000000
SET @v6 = 100.00000000

SELECT @v5= @v1 * @v2 * @v3 / (@v4 * @v6)
SELECT CAST(@v5 AS DECIMAL(18,8))

Probably some guru here will know the technical reason for this.

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 05:04:35

[code]declare @v1 decimal(18,8)
declare @v2 decimal(18,8)
declare @v3 decimal(18,8)
declare @v4 decimal(18,8)
declare @v5 decimal(18,8)
declare @v6 decimal(18,8)

set @v1 = 95008.00000000
set @v2 = 40
set @v3 = 30
set @v4 = 366
set @v6 = 100

select @v5 = @v1 * @v2 * @v3 / (@v4 * @v6) -- select 114009600.00000000 / 36600.00000000
select @v5

-- solution
select @v5 = cast(@v1 * @v2 * @v3 as decimal(18,8)) / cast(@v4 * @v6 as decimal(18,8)) -- select 114009600.00000000 / 36600.00000000
select @v5[/code]

E 12°55'05.25"
N 56°04'39.16"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 05:27:53

Hi Peso,

Why does this happen?

Does the result of the Multiplications get an implicit conversion to a specific data-type?

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 05:51:09

It seems it does, even if all variables are of same and correct type.
This behaviour can also be seen in SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources