# sql - Conversion failed when converting the varchar value '.03' to data type int

I have made a query to calculate minutes in hours that was not done yet, unfortunately I got that error. Also can some one please tell me how can I convert minutes in hours, e.g 1.45hr should equal 1.75hr , 1.30 should be 1.5 and then I want to add them.

``SELECT * ,( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 1, 1) ) AS Hours ,( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 3, 4) ) AS Minutes ,( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 3, 4) * 10 / 60.00 ) AS FractionalHours ,ROUND(( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 3, 4) * 10 / 60.00 ) / 10 + ( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 1, 1) ), 2) AS ActualHoursFROM WorkDoneWHERE NoOfHoursWorked LIKE '%.%'------- UpDate----UPDATE WorkDoneSET NoOfHoursWorked = ROUND(( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)), 3, 4) * 10 / 60.00 ) / 10+ ( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)),1, 1) ), 2)``

You have this expression:

``````SUBSTRING(cast((NoOfHoursWorked) as varchar(500)),3,4)*10/60.00
------------------------------------------------------
``````

You are multiplying a substring by integers. I have no idea what `NoOfHoursWorked` is. It would seem strange to me if this were stored as anything other than a numeric. So, perhaps this will work:

``````NoOfHoursWorked*10/60.00
``````

If you do need the strange string manipulations, just convert the result to a number:

``````cast(SUBSTRING(cast((NoOfHoursWorked) as varchar(500)),3,4) as float) *10/60.00)
``````

if you want to add these decimals such that it give you output like 1.30 + 1.50 + 2.00 + 3.50 = 9.10 then this might be helpful :

``````-- if your column is decimal(x,2)
SELECT  sum(CAST(NoOfHoursWorked as int))
+ sum(cast(Parsename(NoOfHoursWorked,1) as int))/60
+ (sum(cast(Parsename(NoOfHoursWorked,1) as int))%60) *0.01
FROM    yourtable

-- if your column is float or decimal(x,2)
SELECT  sum(CAST(NoOfHoursWorked as int))
+ sum(cast(Parsename(cast((NoOfHoursWorked) as decimal(10,2)),1) as int))/60
+ (sum(cast(Parsename(cast((NoOfHoursWorked) as decimal(10,2)),1) as int))%60) *0.01
FROM    yourtable
``````

SqlFiddle for first query.

SqlFiddle for second query.

NOTE : I found same question here. There are other answers too for same thing. check that out.