Happens occasionally: "Error converting data type nvarchar to float"

Question

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.
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:

Solution 1

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,
  CONVERT(VARCHAR(10), [date], 110)
FROM history
WHERE id IN
(
    SELECT TOP 8 id
    FROM history
    WHERE ([siteName] = 'CAL00022') 
    ORDER BY id DESC
)
AND ISNUMERIC([rssi1]) = 1
ORDER BY date ASC

Or present it as is (each with it's own limitations):

SELECT
  CASE WHEN ISNUMERIC([rssi1]) = 1 THEN CAST((CAST(CAST([rssi1] as float) as int))*-1 as nvarchar) ELSE [rssi1] /* Or choose a value to default to */ END, 
  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

Solution 2

I agree with Blorgbeard's comment above; I'd guess that where you show CAL00022, this is actually a parameter, so I'd suggest writing yourself a little proc to parse your history table for distinct values of sitename, looking for values in rssi1 that wont convert correct;

Something like this: (this is a simple job that should find the sitenames for which bad data exists - though you will then need to examine the actual data existing for that sitename yourself to identify what is incorrect.

(NB: this is a bit of a mess, I just typed it out quickly - it can probably be done much better than this)

CREATE PROCEDURE ParseHistoryData 
AS BEGIN
  SET NOCOUNT ON;

  declare @site varchar(20);
  select distinct sitename into #tmp from history;
  create table #bad (sitename varchar(20));

  while (select COUNT(*) from #tmp) > 0 begin
    select @site = MIN(sitename) from #tmp;
    delete #tmp where sitename = @site;

    select rssi1 into #num from history where sitename = @site;
    select CAST(CAST(rssi1 AS float) AS INT) as casted into #err from #num;

    if @@ERROR <> 0 
      insert #bad values (@site);

    drop table #num;
    drop table #err;
  end 

  select sitename from #bad order by 1;
END