Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sravanim
Yak Posting Veteran
58 Posts |
 Posted - 2014-04-23 : 06:27:45
|
| i have update statement belowUPDATE [DW].[Deliverable] SET [USSites] = CONVERT(NUMERIC(38, 0), S.US_Sites) ,[IntlSites] = CONVERT(NUMERIC(38, 0), S.INTL_Sites) ,[NumberOfUSPatients] = CONVERT(NUMERIC(38, 0), S.Number_of_US_Patients) ,[NumberOfIntlPatients] = CONVERT(NUMERIC(38, 0), S.Number_of_International_Patients),[StudyVisits] = CONVERT(NUMERIC(38, 0), S.Study_Visits) FROM [DW].[Deliverable] D INNER JOIN STG.MSPS_GPS S ON (S.WP_MCode = D.DeliverableUID) WHERE ISNULL(S.IsValid, 1) = 1 when i am executing i got below errorMsg 8114, Level 16, State 5, Line 1Error converting data type nvarchar to numeric. please suggest me how to overcome |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-23 : 08:30:37
|
That means that one or more of the rows have data that cannot be converted to the numeric data type that you are trying to convert it to. One thing you could do is to examine each column - for example, to test if the column US_Sites have non-numeric values, use this querySELECT * FROM STG.MSPS_GPS S WHERE ISNUMERIC(US_Sites) = 0 Similarly for other columns as well. This is not a fool-proof test. It only says whether the value in the column can be converted to some numeric type. So if this does not show you what values are causing the problem, try converting to float. |
 |
|
|
sravanim
Yak Posting Veteran
58 Posts |
Posted - 2014-04-23 : 08:48:15
|
| Actually some rows contain balnk spaces.Is it causing problem? |
|
|
|
|
|
|
|
|
Error converting data type nvarchar to numeric