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,

I also received the message "Error converting data type nvarchar to float". This was from a query in SQL Server Management Studio (SSMS) from SQL Server 2019.

In my case there were undecipherable characters in the source data, which was exported to CSV from Excel. Some columns were in the "Accounting" format, which has spaces, currency symbols, commas, and parenthesis, like this:

- positive values: $ 123,456.78
 - negative values: $(123,456.78)

To account for this and resolve the error, I simply inserted a nested REPLACE() function to remove some characters and replace others, and wrapped it with IsNull() to return zero when necessary.

First I will describe:

• replace any $,) with nothing (aka null string or empty quotes '' )
• replace any ( with - (aka hyphen or negative symbol)

And here is the SQL:

Original:
CONVERT(float, exp_TotalAmount)

Replacement:
CONVERT(float, IsNull(REPLACE(REPLACE(REPLACE(REPLACE(exp_TotalAmount,'(','-'),')',''),',',''),'$',''),0))

Here broken up for easier read, innermost first:
REPLACE(exp_TotalAmount, '(', '-')
REPLACE(X, ')', '')
REPLACE(X, ',', '')
REPLACE(X, '$', '')
IsNull (X, 0)
CONVERT(float, X)

Convert float to nvarchar instead of nvarchar to float. Of course!