KNOWLEDGE BASE

Error "Error converting data type varchar to float” Creating Extract of SQL Server Data Source


Published: 03 Jan 2017
Last Modified Date: 03 Apr 2017

Issue

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

Environment

Tableau Desktop
Microsoft SQL Server 
 

Resolution

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>])

Cause

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-".
Did this article resolve the issue?