Solved

convert varchar to numeric help

Posted on 2014-09-16
6
174 Views
Last Modified: 2014-10-30
Hi

the following fails:

	   declare @vOdometer varchar(50) = 419

	   
	   print convert(numeric,(@vOdometer * 0.1))

Open in new window


any idea what i'm doing wrong?
0
Comment
Question by:websss
  • 4
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40324883
You've got the brackets in the wrong place.

You want to convert @vOdometer to numeric, THEN multiply by 0.1. What you are trying to do it is force an implicit conversion, then an explicit conversion.

Try this:

          declare @vOdometer varchar(50) = 419

        
         print convert(numeric,(@vOdometer)) * 0.1
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324884
or          

declare @vOdometer varchar(50) = 419

         
         print convert(numeric,@vOdometer) * 0.1
0
 

Author Comment

by:websss
ID: 40324985
Thanks
Thats works

But when i try and have it in the SP it missing the .9
the column is :
vOdometer (Numeric(18,0),Null)
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324989
That's because you are using Numeric(18,0) - that means, 18 digits in total, 0 after the decimal point. See http://msdn.microsoft.com/en-gb/library/ms187746.aspx for details.

Therefore, you need to use Numeric(18,1)

(If you need to use 18 in the first place - that sounds like overkill).
0
 

Author Comment

by:websss
ID: 40324990
hmm, i think the column name is wrong
when i manually edit the table and adjust the row it doesn't work either
which data type should i be storing it as?

I guess i'd be storing xx.xx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324991
The minimum you need. How many digits do you need (precision)? How many digits after the decimal point do you need (scale)?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now