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!

• kevin4u06

  SSCrazy

  Points: 2093

  More actions

  December 12, 2011 at 8:47 am

  #1420121

  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

  December 12, 2011 at 8:49 am

  #1420123

  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

  December 12, 2011 at 8:49 am

  #1420124

  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: 6839

  More actions

  December 12, 2011 at 8:51 am

  #1420126

  select 123456 * 0.01

• kevin4u06

  SSCrazy

  Points: 2093

  More actions

  December 12, 2011 at 9:07 am

  #1420138

  THANKS YOU EVRY1

• kinggion79

  Valued Member

  Points: 51

  More actions

  December 13, 2011 at 12:18 am

  #1420376

  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

  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!