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

Question

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 ActualHours
FROM    WorkDone
WHERE   NoOfHoursWorked LIKE '%.%' 
  ------- UpDate----
UPDATE  WorkDone
SET     NoOfHoursWorked = ROUND(( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)),  3, 4) * 10 / 60.00 ) / 10
                                + ( SUBSTRING(CAST(( NoOfHoursWorked ) AS VARCHAR(500)),1, 1) ), 2)

Not able to put image here data is like this 1.30 or 1.3 , 1.50 ,2,3.50 these are hours before decimal and after that are minutes — Samad, Nov 21 '14 at 17:11
You would be a lot better off storing the number of minutes worked than the number of hours, for a variety of reasons. — Dan Bracuk, Nov 21 '14 at 17:11
@Samad : and you want to add these decimals such that it give you output like 1.30 + 1.50 + 2.00 + 3.50 = `9.10`? — Deep, Nov 21 '14 at 17:25
@Deepak: Yes exactly please tell me the formula or trick to solve it, if minutes i calculate seperatly it can b done, but with hours how it will b? — Samad, Nov 21 '14 at 22:16
possible duplicate of [How do I convert a float Value to Time in sql and get its sum?](http://stackoverflow.com/questions/26883574/how-do-i-convert-a-float-value-to-time-in-sql-and-get-its-sum) — Deep, Nov 22 '14 at 06:49

score 1 · Answer 1 · answered Nov 21 '14 at 17:07

1

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)

answered Nov 21 '14 at 17:07

Gordon Linoff

1,242,037
58
646
786

NoOfHoursWorked is hours inserted by employees in decimal formate. – Samad Nov 21 '14 at 17:20
@Samad . . . Then the first answer should work. There is no need to convert it to a string to do arithmetic. In fact, such a conversion seems awkward, to say the least. – Gordon Linoff Nov 21 '14 at 22:45

score 0 · Answer 2 · edited May 23 '17 at 10:33

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.

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

2 Answers2