HELP! Error converting data type varchar to float

HELP !!!

Ladies & Gents

I use this stored proceedure every week and suddenly it stopped
working. I was hoping someone to point me in the right direction in correcting the problem. When executing the sp. I get the following Error... Error converting data type varchar to float.

Here is my stored proceedure:

Alter Procedure sp_project_schedule_test
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As

SELECT Project.Project_ID, substring(Project.Project_Name,1,40) as Project_Name,
Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME AS TERR, Project.Status,
SUM(Activities.Hours_Work) AS Hours_Work,
SUM(Activities.Hours_Travel) AS Hours_Travel,

'Total_Hours' =

SUM(case when

(Activities.Hours_Work + Activities.Hours_Travel) > 0

then (Activities.Hours_Work + Activities.Hours_Travel)

else 0 end),

MIN(CONVERT(datetime, CONVERT(varchar,
DATEPART(m, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(d, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(yy,
Activities.Time_Stamp)))) AS First_Work,
MAX(CONVERT(datetime, CONVERT(varchar, DATEPART(m,
Activities.Time_Stamp)) + '/' + CONVERT(varchar, DATEPART(d,
Activities.Time_Stamp)) + '/' + CONVERT(varchar,
DATEPART(yy, Activities.Time_Stamp)))) AS Last_Work,

'Quoted_Hours' =

case when

(MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours))) > 0

then (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours)))

else 0 end, max(Project.sched_complete_date) as sched_complete_date

FROM dbo.Activities INNER JOIN
dbo.Tasks ON
dbo.Activities.Task_ID = dbo.Tasks.Task_ID INNER JOIN
dbo.Services ON
dbo.Tasks.Service_ID = dbo.Services.ServiceID RIGHT OUTER JOIN
dbo.Project INNER JOIN
dbo.vw_project_last_activity_date ON
dbo.Project.Project_ID = dbo.vw_project_last_activity_date.Project_ID
ON
dbo.Services.ProjectID = dbo.Project.Project_ID LEFT OUTER JOIN
ADI.dbo.[SALES GROUPS] ON
dbo.Project.In_Sls_No = ADI.dbo.[SALES GROUPS].TERR#

WHERE (dbo.Project.Status = N'open') AND dbo.Project.Standard = 'no'
OR
(NOT (dbo.Project.Status LIKE N'cancel%')) AND
(dbo.vw_project_last_activity_date.Last_Activity > { fn NOW()
} - 30) AND dbo.Project.Standard = 'no'

GROUP BY dbo.Project.Project_ID, dbo.Project.Project_Name,
dbo.Project.Status, dbo.Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME
HAVING (NOT (dbo.Project.Project_Name LIKE N'mis test%')) AND
(NOT (dbo.Project.Status = N'CANCELED')) AND
(NOT (dbo.Project.Project_ID = '88888')) AND
(NOT (dbo.Project.Project_ID = '100254'))

order by Project.Project_ID desc


Any help would be greatly appreciated.
Thanks in advance
pharoah34

I suppose you have a string in column Project.Quoted_Tech_Hours which ou are tyring to convert to float.
Try to run this:
SELECT (whatever)
FROM Project
WHERE ISNUMERIC(Quoted_Tech_Hours) = 0

Bambola.

Hello bambola

Thank you so much for your help.
I implemented your suggestion via QA (Query Analyzer)
and it did return the row of results.

Given this result, How can I correct the problem in my stored proceedure.
What would be the next steps.

Thank you so much in advance for any additional assistance you and/or anyone
may be able to provide

Thanks
pharoah35

Let me ask you a question: why is a float type column defined as varchar? If you are handling the inserts yourself and can control this variable before you insert a row, I'd say change the datatype in the table to float and you will have no problems running this store procedure. That would be the best solution.

Otherwise, add a control to this fields to this sproc
WHERE ISNUMERIC(Project.Quoted_Tech_Hours) = 1 and ISNUMERIC(Project.Quoted_Eng_Hours) = 1

If you need to include the lines with the varchar you can repeat the query with UNION ALL
and the a control for ISNUMERIC = 0

By the way, you can rewrite your CASE statement like this:
COALESCE((MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours) + CONVERT(FLOAT, Project.Quoted_Eng_Hours))), 0)

Bambola.

Hello Bambola

First, Thank you so much for your assistance concerning this matter.
Your help is much appreciated.

I attempted to change the data type from nvarchar to float as suggested
but I got the following Error

'Project' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to float.

I am certainly open to any suggestions anyone may have
Please HELP...
pharoah35

First try to run update on the table, changing all the non numeric value into 0 or null (if itis acceptable). Then you should be able to change the datatype.

Bambola.