SQl查询以HH:MM格式计算总计 [英] SQl Query to calculate Total in HH:MM Format

问题描述

我有一个表(生产)，其中有一个名为TimeSpent的列，此列的数据类型为varchar，并且以HH:MM格式存储数据，例如10:23,14:59,11:00等.我想在sql中编写一个查询，它将对此进行汇总并仅以HH:MM格式提供给我总计.我尝试了类似的操作，但收到错误消息``从字符串转换日期和/或时间时转换失败.'':-

I have a table(production) which is having a column with name TimeSpent, the datatype of this column is varchar and it stores data in HH:MM format example 10:23,14:59,11:00 etc.I want to write a query in sql which will sum this and give me a total in HH:MM Format only. I tried some thing like this but getting error ''Conversion failed when converting date and/or time from character string.'':-

select CAST
(
(SUM (datepart(hh, convert (varchar, timespent, 108))) +
(sum(datepart(mi, convert (varchar, timespent, 108)))/60) ) AS VARCHAR(2)
)
+ ':' +
CAST
(
sum(datepart(mi, convert (varchar, timespent, 108))) - 60 * (sum(datepart(mi, convert (varchar, timespent, 108)))/60)
 as VARCHAR(2)) from production

推荐答案

查询对我有用，我刚刚更新了表并减小了大小从varchar(60)到Varchar(25)的时间花费的百分比，它是固定的.

Query worked for me, I had just updated my table and decreased the size of TimeSpent to Varchar(25) from varchar(60) and it is fixed.

select CAST
(
(SUM (datepart(hh, convert (varchar, timespent, 108))) +
(sum(datepart(mi, convert (varchar, timespent, 108)))/60) ) AS VARCHAR(2)
)
+ ':' +
CAST
(
sum(datepart(mi, convert (varchar, timespent, 108))) - 60 * (sum(datepart(mi, convert (varchar, timespent, 108)))/60)
 as VARCHAR(2))from production

这篇关于SQl查询以HH:MM格式计算总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！