How do I add a decimal to a number

Question

Post reply

How do I add a decimal to a number

kevin4u06

SSCrazy

Points: 2093
More actions
December 12, 2011 at 8:39 am

#247511

Hi All,
How do i add a decimal? I have a value 123456 and i have to display it as 1234.56.
Please Help me solve this.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kevin4u06 SSCrazy Points: 2093 More actions · Answer 1

My Approach

select cast(cast(123456 as decimal(10,2))*0.01 as decimal(10,2))

Is there a simple way to do this?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

You should leave the displaying of information to your presentation layer. If you must use T-SQL, this'll get you most of the way:

SELECT 123456 / 100.0

John

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

SELECT 123456 * 0.01

Returns

---------------------------------------

1234.56

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

davidandrews13 SSCertifiable Points: 6809 More actions · Answer 4

davidandrews13

SSCertifiable

Points: 6809

December 12, 2011 at 8:51 am

#1420126

select 123456 * 0.01

kevin4u06 SSCrazy Points: 2093 More actions · Answer 5

kevin4u06

SSCrazy

Points: 2093

December 12, 2011 at 9:07 am

#1420138

THANKS YOU EVRY1

kinggion79 Valued Member Points: 51 More actions · Answer 6

Hi Kevin4u06,

You can write a function to solve your problem. This is function code:

CREATE FUNCTION [dbo].[format_currency] (@monetary_value decimal(20,2) ) returns varchar(20)

as

begin

declare @return_value varchar(20)

declare @is_negative bit

select @is_negative = case when @monetary_value<0 then 1 else 0 end

if @is_negative = 1

set @monetary_value = -1*@monetary_value

set @return_value = convert(varchar, isnull(@monetary_value, 0))

--------------------------------------------------------------------------------

declare @before varchar(20), @after varchar(20)

if charindex ('.', @return_value )>0

begin

set @after= substring(@return_value, charindex ('.', @return_value ), len(@return_value))

set @before= substring(@return_value,1, charindex ('.', @return_value )-1)

end

else

begin

set @before = @return_value

set @after=''

end

-- after every third character:

declare @i int

if len(@before)>3

begin

set @i = 3

while @i>1 and @i < len(@before)

begin

set @before = substring(@before,1,len(@before)-@i) + ',' + right(@before,@i)

set @i = @i + 4

end

set @return_value = @before + @after

if @is_negative = 1

set @return_value = '-' + @return_value

return @return_value

end

kevin4u06 (12/12/2011)
Hi All,
How do i add a decimal? I have a value 123456 and i have to display it as 1234.56.
Please Help me solve this.
Thanks!