Error "Error converting data type varchar to float” Creating Extract of SQL Server Data Source
Published: 03 Jan 2017 Last Modified Date: 27 Jul 2018
When you try to extract a SQL Server data source, one of the following errors might occur:
An error occurred while communicating with data source <Data Source Name>. SQL Server database error 0x80040E07: Error converting data type varchar to float.
[Microsoft][SQL Server Native Client 11.0][SQL Server] Error converting data type varchar to float Unable to create extract
Tableau Desktop Microsoft SQL Server
Use one of the following options, as appropriate to your data:
Create a calculated field to strip currency symbols and commas from your data before converting it.
Ensure that characters such as the plus (+) sign, minus (-) sign and decimal point (.) are positioned in a valid number format. For example, plus and minus signs can only be used to the left of a number (For example, -2).
If your string data values are currency, you can use rawSQL to cast the string to SQL Server's MONEY data type before casting to a number. For example:
RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [<Your_String_Field>])
This error occurs because SQL Server is unable to convert string values to a numeric value if the string contains the following:
Currency symbol such as $, £, or ¥. For example, "$123".
Commas. For example, "100,000".
A single character of: currency symbol, comma(,), decimal point (.), plus (+) sign, or minus (-) sign. For example, "$".
More than one period. For example, "1.1.0".
Incorrectly positioned plus (+) or minus (-) signs. For example, "2-".
Thank you for providing your feedback on the effectiveness of the article.