Post reply

CONVERT varchar (50) to Decimal (4,4), 2012 Server Express

  • September 10, 2012 at 3:18 pm

    #267585

    Greetings,

    I'm about as newbie as they come.

    I am using SQL Server Management Studio Express 2012 on a Win7 machine.

    BACKGROUND: I have imported data (in sections) to several tables from an excel spreadsheet 30 columns wide and containing 338 records. The only way I was able to do this was copy like data into separate Excel spreadsheets, convert to txt and import to a table.

    ISSUE: 2 of the columns contain numeric frequencies but are now varchar (50). The format is 000.000. I would like to convert the data in these columns to decimal (4.4) (just to be safe). Can anyone tell me if this is possible? And, if so, how? If not, is there a better way to import the data to make this possible?

    Appreciate your help.

    Thanks,

    cjamt

  • September 10, 2012 at 3:22 pm

    #1535938

    Try NUMERIC(8,4).

  • September 10, 2012 at 3:40 pm

    #1535939

    SSCrazy:

    It worked!!!! Imagine that. Thanks. CJAMT:-D

  • September 11, 2012 at 9:14 am

    #1536322

    The reason why decimal(4,4) doesn't work is because the scale is equal to precision, and that doesn't fit with the number format you have.

    http://msdn.microsoft.com/en-us/library/ms187746.aspx

  • September 11, 2012 at 9:26 am

    #1536333

    Decimal 4, 4 allows for 4 digits, all 4 to the right of the decimal. So it stores values between 0 and 0.9999 (on the positive side), but it cannot store 1.0000 as that requires 5 digits, not 4.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 11, 2012 at 3:50 pm

    #1536513

    Gail: Thanks for the response.

    So, If I were to use decimal for this application to store frequencies in the format of 0000.0000, I would need decimal (8, 4) Correct. Like using Numeric (8,4). For the purposes of frequencies, with a possible need to do addition/subtraction of standard offsets say 6Mhz, which data type should I use? Is there a benefit of one over the other in this application?

    Again, Thanks.

    cjamt

  • September 11, 2012 at 3:55 pm

    #1536515

    adminorama:

    Thanks for the response! I was wondering if there was a preference of decimal over numeric in my situation. Please see my post to Gail.

    Thanks again for your response.

    CJAMT

  • September 11, 2012 at 4:17 pm

    #1536524

    Numeric and decimal are identical data types.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 12, 2012 at 6:01 am

    #1536666

    Gail,

    Thanks again. I guess may issue was providing enough room on either side of the decimal that caused the conversion error. Thanks for your patience.

    cjamt

Viewing 9 posts - 1 through 8 (of 8 total)

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