Data Conversion ( varchar to binary)

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  January 15, 2005 at 8:39 pm

  #183938

  Hi,

  I am having trouble in convert the data from one data type to another. I have a column which is declared as varchar(100) and it contains decimal values like

  column

  --------

  0.4567777777778999999999345677777777777777777

  10.3456789002322222222222545465565767888888888888888

  I want to convert this and place into another column which is of type decimal(10,4). The problem is the column contains more than 38 digits which is the maximum precision for decimals provided by sql server. So I'm getting error 'arithmetic out of range exception' while trying to use convert function. How should I do this? Please let me know.

  I need the result to be

  column

  ---------

  0.4567

  10.3456

  Thanks,

  Sridhar!!

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  January 16, 2005 at 6:36 am

  #537590

  It looks like you want to round down in all cases - this code should do the trick:

  declare @a varchar(100), @b-2 varchar(100)

  set @a = '0.4567777777778999999999345677777777777777777'

  set @b-2 = '10.3456789002322222222222545465565767888888888888888'

  select cast(left(@a, charindex('.', @a)+4) as decimal(10,4)), cast(left(@b, charindex('.', @b-2)+4) as decimal(10,4))

  Regards

  Phil

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  January 16, 2005 at 8:25 am

  #537592

  Hi Phil,

  Thanks for the reply. It worked.

  Thanks,

  Sridhar!!