Solved

convert varchar to numeric help

Posted on 2014-09-16
6
175 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

778 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