Solved

convert varchar to numeric help

Posted on 2014-09-16
6
176 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

Suggested Solutions

Title # Comments Views Activity
SQL Server / Update DB? 22 38
TSQL XML Namespaces 7 24
reccommendations for a free msft sql query manager? 4 29
Testing for Null in Stored Proc Return Value 5 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

830 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