Zeros with decimals.. - 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 2005 Forums

Transact-SQL (2005)

Zeros with decimals..

Author

Topic

John_Mc_2009
Starting Member

7 Posts

Posted - 2010-04-01 : 15:21:35

Hi all,

I am on SQL server 2005 query analyser. We have statement like the below.

select @total = convert(numeric(9,4), ((tot1 -@tot2 -@tot3)/isnull(@maxTot,1)))

So when we have values like below
select @total = ((68-0.00-0.00)/61), it returns "1.114754".

But, what we are looking is select @total= ((68-0-0)/61), so that we get "1".

We cannot use round for the @total.

Please advice, how to get value "1" without any decimals for the above example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 15:24:20

use FLOOR() then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

John_Mc_2009
Starting Member

7 Posts

Posted - 2010-04-01 : 15:36:58

Floor () did not helped. I got the same answer.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-01 : 15:40:56

FLOOR works, you can also just CAST the value as an INTEGER..

DECLARE @Foo TABLE (Val DECIMAL(18,5))

INSERT @Foo
SELECT 1.999
UNION ALL SELECT 1
UNION ALL SELECT 1.001

SELECT ROUND(Val, 0), FLOOR(Val), CAST(Val AS INT)
FROM @Foo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-01 : 15:42:52

What data type is @total? Do you need to FLOOR or CAST that?

John_Mc_2009
Starting Member

7 Posts

Posted - 2010-04-01 : 16:18:48

All variables are numeric(9,4).

I tried select @total = convert(numeric(9,4), ((tot1 -cast(@tot2 as INT) -cast(@tot3 as INT))/isnull(@maxTot,1)))

but it did not give me "1", but "1.114"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-01 : 16:26:31

Of course, you've converted the value to a NUMERIC!!

select @total = convert(numeric(9,4), ((tot1 -cast(@tot2 as INT) -cast(@tot3 as INT))/isnull(@maxTot,1)))

select @total = convert(numeric(9,4), ((tot1 -@tot2 -@tot3)/isnull(@maxTot,1))) 
SELECT FLOOR(@total)
-- OR 
SELECT CAST(@total AS INT)

DECLARE @total as INT
SET @Total = FLOOR(((tot1 -@tot2 -@tot3)/isnull(@maxTot,1)))
-- OR
SET @Total = CAST(((tot1 -@tot2 -@tot3)/isnull(@maxTot,1)) AS INT)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources