Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CONVERT varchar (50) to Decimal (4,4), 2012 Server Express Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:17 AM
Points: 7, Visits: 26
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
Posted Monday, September 10, 2012 3:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Try NUMERIC(8,4).
Post #1357033
Posted Monday, September 10, 2012 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:17 AM
Points: 7, Visits: 26
SSCrazy:

It worked!!!! Imagine that. Thanks. CJAMT
Post #1357036
Posted Tuesday, September 11, 2012 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:44 AM
Points: 9, Visits: 75
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
Posted Tuesday, September 11, 2012 9:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 42,466, Visits: 35,529
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
Posted Tuesday, September 11, 2012 3:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:17 AM
Points: 7, Visits: 26
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
Posted Tuesday, September 11, 2012 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:17 AM
Points: 7, Visits: 26
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
Posted Tuesday, September 11, 2012 4:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 42,466, Visits: 35,529
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
Posted Wednesday, September 12, 2012 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:17 AM
Points: 7, Visits: 26
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 »

Add to briefcase

Permissions Expand / Collapse