SQL Server Performance

HELP! Error converting data type varchar to float

Discussion in 'General Developer Questions' started by pharoah35, Jun 23, 2003.

  1. pharoah35 New Member

    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

    SELECT Project.Project_ID, substring(Project.Project_Name,1,40) as Project_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
    dbo.Services.ProjectID = dbo.Project.Project_ID LEFT OUTER JOIN
    dbo.Project.In_Sls_No = ADI.dbo.[SALES GROUPS].TERR#

    WHERE (dbo.Project.Status = N'open') AND dbo.Project.Standard = 'no'
    (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,
    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
  2. bambola New Member

    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

  3. pharoah35 New Member

    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

  4. bambola New Member

    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)

  5. pharoah35 New Member

    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...

  6. bambola New Member

    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.


Share This Page