Error Converting Data Type varchar to float

• greg.whittaker

  Valued Member

  Points: 54

  More actions

  February 11, 2010 at 4:16 pm

  #210866

  I am having an issue with a query being run on a sql server 2008 database. I am attempting to extract data with the following query but am receiving the error in the subject line above.

  SELECT *

  FROM BPS_Patients

  WHERE StatusText = 'Active'

  AND DOB < DateAdd(Year, -45, GetDate())

  AND DOB > DateAdd(Year, -50, GetDate())

  AND InternalID IN (SELECT InternalID FROM Observations WHERE DataCode = 9 AND CAST(DataValue AS float) >= 30 AND RecordStatus = 1)

  ORDER BY surname, firstname

  Any help would be appreciated

  Greg

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  February 11, 2010 at 4:40 pm

  #1117837

  What kind of values exist in the DataValue column and what is the actual data type for that column? Why are you trying to convert/cast that value to a float and then compare it with an integer?

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 11, 2010 at 4:48 pm

  #1117841

  I guess you have variable "types" of data in DataValue - hence the check for DataCode=9 ?

  In which case you will need to make SQL filter by DataCode before it checks the DataValue.

  If you check the execution plan, it will almost certainly be testing both conditions in the same pass and will therefore come across non-numeric values in DataValue.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]