Error Converting Data Type Nvarchar To Float Sql 2008
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn error converting data type varchar to float. sql server 2008 more about Stack Overflow the company Business Learn more about hiring developers or
Error Converting Data Type Varchar To Float
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community
Msg 8114 Level 16 State 5 Line 1 Error Converting Data Type Varchar To Float
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Happens occasionally: “Error converting data type nvarchar to float” up
T-sql Error Converting Data Type Varchar To Float
vote 2 down vote favorite Here is my SQL query: SELECT (CAST(CAST([rssi1] AS float) AS INT))*-1, CONVERT(VARCHAR(10), [date], 110) FROM history WHERE id IN ( SELECT TOP 8 id FROM history WHERE ([siteName] = 'CAL00022') ORDER BY id DESC ) ORDER BY date ASC Most of the time, it works fine. Sometimes, I get this error: Server Error in '/' Application. Error converting data type nvarchar to float. msg 8114 level 16 state 5 line 2 error converting data type varchar to float Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Error converting data type nvarchar to float. The table is this: sql sql-server casting share|improve this question asked Jul 4 '13 at 22:21 Ned 51041227 Is that your entire table? You probably have at least one value in rssi1 that isn't a valid float.. –Blorgbeard Jul 4 '13 at 22:26 Well, that's obviously not the data that's causing the problem. What does SELECT * FROM history ORDER BY id show you about the rest of the data? –Ken White Jul 4 '13 at 22:28 add a comment| 2 Answers 2 active oldest votes up vote 4 down vote accepted Terrible when you distrust the handling of real numbers in your chosen engine so much that you'll store them in nvarchars! I've retrieved enough 1.000000000001's to sympathise, but don't much like this solution either. Identifying your invalid records, as per John's answer, is necessary but you may not be in a position to personally do anything about it anyway. What you've provided is a S
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting sql server msg 8114 error converting data type nvarchar to numeric ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join error converting data type nvarchar to real. sql server the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a error converting data type nvarchar to bigint in sql server 2008 minute: Sign up Error unable to convert data type nvarchar to float up vote 1 down vote favorite I have searched both this great forum and googled around but unable to resolve this. We have two tables (and trust me I http://stackoverflow.com/questions/17478755/happens-occasionally-error-converting-data-type-nvarchar-to-float have nothing to do with these tables). Both tables have a column called eventId. However, in one table, data type for eventId is float and in the other table, it is nvarchar. We are selecting from table1 where eventI is defined as float and saving that Id into table2 where eventId is defined as nvarchar(50). As a result of descrepancy in data types, we are getting error converting datatype nvarchar to float. Without fooling around with the database, I would like to http://stackoverflow.com/questions/26765604/error-unable-to-convert-data-type-nvarchar-to-float cast the eventId to get rid of this error. Any ideas what I am doing wrong with the code below? SELECT CAST(CAST(a.event_id AS NVARCHAR(50)) AS FLOAT) event_id_vre, sql sql-server share|improve this question edited Nov 5 '14 at 19:53 marc_s 452k938641029 asked Nov 5 '14 at 19:37 Chidi Okeh 72921432 Have you read this: msdn.microsoft.com/en-us/library/ms191530%28v=sql.105%29.aspx ? –Sybren Nov 5 '14 at 19:40 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted The problem is most likely because some of the rows have event_id that is empty. There are two ways to go about solving this: Convert your float to nvarchar, rather than the other way around - This conversion will always succeed. The only problem here is if the textual representations differ - say, the table with float-as-nvarchar uses fewer decimal digits, or Add a condition to check for empty IDs before the conversion - This may not work if some of the event IDs are non-empty strings, but they are not float-convertible either (e.g. there's a word in the field instead of a number). The second solution would look like this: SELECT case when a.eventid <> '' then cast(cast(a.event_id as nvarchar(50)) as float) ELSE 0.0 END AS event_id_vre, share|improve this answer edited Nov 5 '14 at 20:15 HaveNoDisplayName 5,573132034 answered Nov 5 '14 at 19:42 dasblinkenlight 455k39492843 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using G
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » T-SQL http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx » Error converting data type nvarchar to float 29 posts,Page 1 of 3123»»» Error converting data type nvarchar to float Rate Topic Display Mode Topic Options Author Message peter478peter478 Posted Monday, May 18, 2009 7:00 AM Valued Member Group: General Forum Members Last Login: Today @ 12:01 AM Points: 74, Visits: 255 Hi I am testing one stored procedure.I have this Test_table :id error converting intname varchardata float I would like to update all records by condidtion:Update Test_table set data = '27.55' where name = 'Peter'When I tested it and appiers error with converting nvarchar to float .Could you help me please.Thanks Post #719013 John-150025John-150025 Posted Monday, May 18, 2009 7:29 AM SSC Rookie Group: General Forum Members Last Login: Thursday, April 9, 2015 10:20 AM Points: 43, Visits: error converting data 223 Hi, can you post the stored procedure code? Post #719042 peter478peter478 Posted Tuesday, May 19, 2009 2:15 AM Valued Member Group: General Forum Members Last Login: Today @ 12:01 AM Points: 74, Visits: 255 ALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsDECLARE @Nplan as char(20)DECLARE @Ncas as float(2)SET @Nplan = @e_NplanSET @Ncas = CONVERT(float(2) , @e_Ncas)Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplanreturn Post #719592 Christopher StobbsChristopher Stobbs Posted Tuesday, May 19, 2009 2:34 AM SSCommitted Group: General Forum Members Last Login: Wednesday, February 24, 2010 4:10 AM Points: 1,550, Visits: 2,232 Ok you need to make some var changes.You are using a CHar(20) which has a set lenght and SQL is struggling to convert that.Have you tried changed @e_Ncas to a Float in the Procedure declaration?I can't see any reason why it is a char to begin with...ThanksChris ----------------------------------------------Try to learn something about everything and everything about something. - Thomas Henry Huxley Posting Best PracticesNumbers / Tally TablesSQL-4-Life Post #719596 SergiySergiy Posted Tuesday, May 19, 2009 3:52 AM SSCertifiable Group: General Forum Members Last Login: Today @ 4:23 PM Points: 5,570, Visits: 10,798 peter478 (5/19/2009)ALTER Pr