Error Converting Data Type Nvarchar To Float Numeric
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 more about Stack Overflow the company Business Learn
Error Converting Data Type Nvarchar To Float Sql Server 2008
more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags error converting nvarchar to float sql server Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, convert nvarchar to float helping each other. Join them; it only takes a minute: Sign up Happens occasionally: “Error converting data type nvarchar to float” up vote 2 down vote favorite Here is my SQL query: SELECT (CAST(CAST([rssi1] AS float) AS INT))*-1,
Error Converting Data Type Nvarchar To Float Sql Server 2005
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. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error
Error Converting Data Type Nvarchar To Real.
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 SELECT statement that sometimes fails and, so, I address that failure. Checking that the value of rssi1 is numeric prior to attempts at casting can avoid the error you're sometimes getting. You can either exclude those records where rssi1 is not numeric: SELECT (CAST(CAST([rssi1] AS float) AS INT))*-1,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and error converting data type nvarchar to numeric sql server 2008 policies of this site About Us Learn more about Stack Overflow the
Error Converting Data Type Nvarchar To Numeric. In Sql Server 2012
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags error converting data type nvarchar to float sql server 2012 Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only http://stackoverflow.com/questions/17478755/happens-occasionally-error-converting-data-type-nvarchar-to-float takes a minute: Sign up SQL Server 2008: Error converting data type nvarchar to float up vote 3 down vote favorite Presently troubleshooting a problem where running this SQL query: UPDATE tblBenchmarkData SET OriginalValue = DataValue, OriginalUnitID = DataUnitID, DataValue = CAST(DataValue AS float) * 1.335 WHERE FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' AND ZEGCodeID IN (SELECT ZEGCodeID FROM tblZEGCode WHERE(ZEGCode = 'C004') OR http://stackoverflow.com/questions/9136722/sql-server-2008-error-converting-data-type-nvarchar-to-float (LEFT(ZEGParentCode, 4) = 'C004')) Results in the following error: Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float. The really odd thing is, if I change the UPDATE to SELECT to inspect the values that are retrieved are numerical values: SELECT DataValue FROM tblBenchmarkData WHERE FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' AND ZEGCodeID IN (SELECT ZEGCodeID FROM tblZEGCode WHERE(ZEGCode = 'C004') OR (LEFT(ZEGParentCode, 4) = 'C004')) Here are the results: DataValue 2285260 1205310 Would like to use TRY_PARSE or something like that; however, we are running on SQL Server 2008 rather than SQL Server 2012. Does anyone have any suggestions? TIA. sql-server-2008 casting floating-point nvarchar share|improve this question edited May 24 '12 at 15:27 mskfisher 1,94022036 asked Feb 3 '12 at 23:07 user89861 1,65442240 add a comment| 3 Answers 3 active oldest votes up vote 5 down vote accepted It would be helpful to see the schema definition of tblBenchmarkData, but you could try using ISNUMERIC in your query. Something like: SET DataValue = CASE WHEN ISNUMERIC(DataValue)=1 THEN CAST(DataValue AS float) * 1.335 ELSE 0 END share|improv
SQL Server experts to answer whatever question you can come up with. Our new SQL http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165602 Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums General SQL Server Forums New to SQL Server Programming Error nvarchar to converting data type nvarchar to float? Reply to Topic Printer Friendly Author Topic cdik88 Starting Member Malaysia 2 Posts Posted-09/20/2011: 02:26:27 I need to download data from one database to another database, so i am using this query:INSERT INTO GSC2.DBO.salebill SELECT *FROM VGP3.DBO.salebill then it gives a error msg as:Server: Msg 8114, Level 16, error converting data State 5, Line 1Error converting data type nvarchar to float.can someone help me with this problem???c_ flamblaster Constraint Violating Yak Guru 384 Posts Posted-09/20/2011: 02:32:31 It looks like you have a character field (nvarchar) that is not numeric in table VGP3.dbo.salebill that you're trying to insert into a numeric (float) field in GSC2.dbo.salebill. cdik88 Starting Member Malaysia 2 Posts Posted-09/20/2011: 02:35:51 yes i know it...then u know query to convert it to float~c_ flamblaster Constraint Violating Yak Guru 384 Posts Posted-09/20/2011: 02:56:26 Do you know which column it is? What data is in the character string? If it's not numeric, it won't convert, so you'd have to exclude those rows.You can do a straight conversion like this: convert(float, yourcolumn), but it's going to fail like I said if there are characters.Check out this post, very good explanation of some of these concept:http://sqlinthewild.co.za/index.php/2011/07/26/goodbye-isnumeric-hell/ Kristen Test United Kingdom 22859 Posts Posted-09/20/2011: 03:06:33 This should help find it: SELECT Col1, Col2, ... FROM MyTable