• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 904
  • Last Modified:

Error converting data type nvarchar to float

select
*,  BILL_FACTOR*convert (float, AWP_PRICE)
from dbo.Awpmed

[BILL_FACTOR] [float] NULL ,
[AWP_PRICE] [nvarchar] (255)  NULL ,

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

How to do?
0
JohnLucania
Asked:
JohnLucania
4 Solutions
 
nito8300Commented:
First perform a group by on AWP_PRICE to make sure all values can be converted to float.
0
 
nito8300Commented:
or you can try getting rid of records like ' '

select *,  BILL_FACTOR*convert (float, LTRIM(RTRIM(AWP_PRICE)))
from dbo.Awpmed

Hope this helps.
0
 
JohnLucaniaAuthor Commented:
Why do we need 'group by' here??
I need to run the calculation for each.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
JohnLucaniaAuthor Commented:
still gave me:


Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
0
 
nito8300Commented:
The idea is to browse thru the values and see if any are invalid to convert to float.

run this statement and see if you see invalid values
select AWP_PRICE
from dbo.Awpmed
group by AWP_PRICE
ORDER by AWP_PRICE

and the again

select AWP_PRICE
from dbo.Awpmed
group by AWP_PRICE
ORDER by AWP_PRICE DESC
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think there may be values having more than one decimal point
run this

select * from Awpmed where AWP_PRICE like '%.%.%'
0
 
nmcdermaidCommented:
A quick way to find your non-numeric values

select *
from dbo.Awpmed
where ISNUMERIC(AWP_PRICE) = 0

This will list all your non numeric figures which will need to be cleaned up before they can be converted to a numeric type.
0
 
imran_fastCommented:
try this
======
select *,  BILL_FACTOR*convert (float, replace(AWP_PRICE,' ',''))
from dbo.Awpmed
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now