Msg 235, Level 16, State 0, Line 28, Cannot convert a char value to money. The char value has incorrect syntax.

• TCCaines

  Mr or Mrs. 500

  Points: 551

  More actions

  June 23, 2011 at 1:58 pm

  #253259

  We had a SQL statement that is similar to the following on SQL 2008 R2 (10.50.1600) on a 64-bit server:

  SELECT distinct cast(t1.columnvalue as money), t1.ColumnValue

  FROM dbo.table1 t1

  INNER JOIN dbo.table2 t2 ON t2.SystemObjectFieldID=t1.SystemObjectFieldID

  WHERE t1.FieldName1 = 'ListingPrice_Min'

  This statement ran with no errors.

  We recently installed CU7 (10.50.1777) and are now receiving the following error:

  Msg 235, Level 16, State 0, Line 28

  Cannot convert a char value to money. The char value has incorrect syntax.

  I can switch to a 32-bit machine with 10.50.1790 and again the statement runs with no errors. The column being cast is varchar(250) with the value 1000000.

  Can anyone shed any light on this issue?

  I'm wondering if this is a known problem and if it is fixed by CU8?

  Thanks,

  Terry Caines

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  June 23, 2011 at 3:28 pm

  #1343412

  SQL Server created an execution plan based on certain costs.

  So there is no guarantee your multi-use of ColumnValue column is always the same.

  If SQL Server thinks casting all values to money before filtering is less costly than the opposite way, SQL Server will do that.

  Try this to avoid the cost-based behaviour

  SELECT DISTINCTCASE ISNUMERIC(t1.ColumnValue)

  WHEN 1 THEN CAST(t1.ColumnValue AS MONEY)

  ELSE NULL

  END,

  t1.ColumnValue

  FROMdbo.Table1 AS t1

  INNER JOINdbo.Table2 AS t2 ON t2.SystemObjectFieldID = t1.SystemObjectFieldID

  WHEREt1.FieldName1 = 'ListingPrice_Min'

  ---

  N 56°04'39.16"
  E 12°55'05.25"