Possible Duplicate:
Truncate (not round) decimal places in SQL Server
Can't figure this out. I need 1 decimal place returned while SQL is rounding to whole numbers.
I read that integers divided by integers give integers in SQL, but I need the one truncated decimal place for value of output in the temp table.
I don't mind if 35.0 comes back as 35 but 35.17 should come back as 35.1. Sorry just edited. Need to truncate the last number, not round up.
create table #blah(output decimal(9,1))
DECLARE @a money
DECLARE @b money
DECLARE @intinterval decimal(9,1)
SET @a = 5
SET @b = 2
SET @intinterval = (@b / 1000.0) * (86400.0 / @a)
INSERT INTO #blah (output) VALUES (@intinterval)
SELECT * from #blah
drop table #blah
The above equation should give (2 / 1000) * (86400 / 5) = (0.002 * 17280) = 34.56
The 34.56 should truncate to 34.5
SET @intinterval = cast(10 * (@b / 1000.0) * (86400.0 / @a) as int) / 10.0
or
SET @intinterval = cast(@b * 864.0 / @a as int) / 10.0
What about Round((@b / 1000.0) * (86400.0 / @a), 1, 1), where last 1 saying to truncate instead of round.
try this with no special functions...
if
a = 5 then output = 34.5 (34.56)
a = 7 output = 24.6 (24.69)
a = 11 output = 15.7 (15.71)
create table #blah(output decimal(9,1))
DECLARE @a money
DECLARE @b money
DECLARE @intinterval decimal(9,2)
declare @rounded decimal(9,1)
declare @diff decimal(9,2)
declare @finalvalue decimal(9,1)
SET @a = 5
SET @b = 2
SET @intinterval = (@b / 1000.0) * (86400.0 / @a)
set @rounded = @intinterval -- gets the rounded value
set @diff = @intinterval - @rounded -- gets the difference whether to round off or not
if @diff >= 0 -- if differnce is >= 0 then get the rounded value .. eg. 34.31 = 34.3
set @finalvalue = @rounded
else -- else subtract 0.1 and get the rounded value e.g. 34.56 - 0.1 = 34.46 -> rounded value of 34.5
set @finalvalue = @intinterval - 0.1
INSERT INTO #blah (output) VALUES (@finalvalue )
SELECT * from #blah
drop table #blah
