当前位置: 动力学知识库 > 问答 > 编程问答 >

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: