当将varchar值“.03”转换为数据类型int时,转换失败int
我已经做了一个查询来计算还未完成的分钟数,不幸的是我得到了那个错误。也有人可以告诉我如何在几个小时内转换分钟数,例如1.45小时应该等于1.75小时,1.30应该是1.5,然后我想添加它们。当将varchar值“.03”转换为数据类型int时,转换失败int
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)
你有这样的表达:
SUBSTRING(cast((NoOfHoursWorked) as varchar(500)),3,4)*10/60.00
------------------------------------------------------
你被整数相乘的子字符串。我不知道NoOfHoursWorked
是什么。这对我来说似乎很奇怪,如果它被存储为数字以外的任何东西。所以,也许这将工作:
NoOfHoursWorked*10/60.00
如果你需要的怪字符串操作,只是结果转换为数字:
cast(SUBSTRING(cast((NoOfHoursWorked) as varchar(500)),3,4) as float) *10/60.00)
NoOfHoursWorked是员工在十进制格式中插入的小时数。 – Samad 2014-11-21 17:20:51
@萨马德。 。 。然后,第一个答案应该工作。没有必要将其转换为字符串来进行算术运算。事实上,至少可以这样说,这种转变看起来很尴尬。 – 2014-11-21 22:45:33
如果要添加这些小数,它给你像1.30 + 1.50 + 2.00 + 3.50 = 9.10 输出则这可能是有益的:
-- 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为第一查询。
SqlFiddle第二查询。
注意:我发现相同的问题here。同样的事情也有其他答案。检查出。
您的NoOfHoursWorked实际上看起来像什么? – Andrew 2014-11-21 17:06:09
您可以分享'NoOfHoursWorked'列的样本数据吗? – 2014-11-21 17:06:33
无法将图像放在这里数据就像这样1.30或1.3,1.50,2,3.50这些是小数点前几小时,之后是分钟 – Samad 2014-11-21 17:11:27