Post reply

converting number to nvarchar - rounding issues!

  • October 15, 2008 at 6:58 am

    #383091

    Hello!

    I have a linked server (an excel spreadsheet) holding some geocoded data

    If I run a select on the linked server, say on the longtitude, I might get a value such as -4.394042

    If I import this into sql server (into an nvarchar(50) field) it seems to round it. The number above comes out as -4.39404

    Any ideas why it's doing this and how to get around it? I've tried various converts but nothing is doing it properly. I can't seem to find a way to take a numeric value of this sort 'as is'

    Thanks!

    K

  • October 15, 2008 at 8:25 am

    #885255

    I think the simple answer is to not import it into a varchar field.

    Check out the numeric data type. It may suit your needs better.

  • October 15, 2008 at 8:43 am

    #885278

    I was trying to avoid doing that for various crm related reasons - but that's probably the best way to do it. Gave it a go and it seems OK

    Still I'm curious why sql does the rounding up the way it does when converting the value..

    cheers

  • October 15, 2008 at 8:58 am

    #885292

    It must be the way SQL handles floats and reals, which on import it is probably using. In my quick experiment, a cast to numeric forces SQL out to the precision you define.

    Try this script

    declare @val1 float

    Set @val1 = -4.394042

    declare @val2 real

    Set @val2 = -4.394042

    declare @val3 numeric(20,9)

    Set @val3 = -4.394042

    Select @val1, cast(@val1 as varchar), cast(cast(@val1 as numeric(20,6)) as varchar)

    ,@val2, cast(@val2 as varchar)

    ,@val3, cast(@val3 as varchar)

Viewing 4 posts - 1 through 3 (of 3 total)

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