Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    47

    Unanswered: numeric conversion

    I have a source table with a varchar field like 0000005467.

    My target table has a numeric 18,2 column which I am trying to populate with 54.67 but it keeps rounding the last 2 digits to ZERO's.

    Any ideas?

    -K

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try something likes that:

    declare @str varchar(25)
    select @str='00005667'
    select @str=left(@str,len(@str)-2)+'.'+right(@str,2)
    select convert(decimal(10,2),@str),@str

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The data field numeric(18,2) will support 2 decimal places but it doesn't assume that a value stored or converted has a 2 decimal value. So you need to tell the system that the converted value has a 2 decimal value, divide by 100.

    declare @x varchar(15)

    set @x = '0000005467'

    select convert(numeric(18,2),@x)/100
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •