Decimal reduction with ease

A co-worker of mine just asked me today how the best way to get a some percentages outputted with just two decimal - like 0.00 up til 100.00.

Well the simple answer is just casting the output to decimal(5,2). This decimal floater type can hold five digits where 2 of them will be after the decimal point.

So select cast(100 as decimal(5,2)) will output 100.00

A simple example is here:


set nocount on
/* Make a demo table holding the numbers */
declare @numbers table(num float)
/* Counter and a stop number */
declare @num float = 1, @maxnum float = 10000
/* Insert the counter and count it up by something until we reach the stop number*/
while @num < @maxnum
begin
insert @numbers select @num
set @num = @num + 12.3456789
print @num
end
/* select the output and show the casted decimal(5,2) number */
select num as Number, num/@maxnum*100 as FloatNumber, cast(num/@maxnum*100 as decimal(5,2)) as DecimalNumber from @numbers