Solved

convert varchar to numeric help

Posted on 2014-09-16
6
172 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
SQL Query Syntax Error 9 33
SQl query 19 13
SQL Maintenance Plan 3 16
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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 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

19 Experts available now in Live!

Get 1:1 Help Now