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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » CONVERT varchar (50) to Decimal (4,4), 2012...

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

Rate Topic

Display Mode

Topic Options

Author

Message

cjamt

Posted Monday, September 10, 2012 3:18 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:13 AM
Points: 7, Visits: 20

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

Post #1357032

ColdCoffee

Posted Monday, September 10, 2012 3:22 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

Try NUMERIC(8,4).

Post #1357033

cjamt

Posted Monday, September 10, 2012 3:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:13 AM
Points: 7, Visits: 20

SSCrazy:

It worked!!!! Imagine that. Thanks. CJAMT BigGrin

Post #1357036

adminorama

Posted Tuesday, September 11, 2012 9:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 2:05 AM
Points: 9, Visits: 67

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

Post #1357506

GilaMonster

Posted Tuesday, September 11, 2012 9:26 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

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 2008, MVP
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

Post #1357518

cjamt

Posted Tuesday, September 11, 2012 3:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:13 AM
Points: 7, Visits: 20

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

Post #1357727

cjamt

Posted Tuesday, September 11, 2012 3:55 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:13 AM
Points: 7, Visits: 20

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

Post #1357729

GilaMonster

Posted Tuesday, September 11, 2012 4:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

Numeric and decimal are identical data types.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1357738

cjamt

Posted Wednesday, September 12, 2012 6:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:13 AM
Points: 7, Visits: 20

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

Post #1357920

« Prev Topic | Next Topic »

Permissions