如何显示小时和分钟的小时数 [英] How to display hours worked with hours and minutes

问题描述

下面是我的查询和我的c＃代码问题是我需要以小时和分钟显示总和但是我收到此错误聚合函数的使用无效Sum（）和类型：String.How我可以解决它吗？ />

Below is my query and my c# code the problem is i need to display the Sum in hours and min but i am getting this error Invalid usage of aggregate function Sum() and Type: String.How can i solve it

CAST(DATEDIFF(Minute,FromDate, ToDate) / 60 AS VARCHAR(5)) + ' Hrs' + ':' + RIGHT('0' + CAST(DATEDIFF(Minute, FromDate, ToDate) % 60 AS VARCHAR(2)), 2) + ' Min' AS 'Hr'

我尝试过：



我试过

What I have tried:

I tried

var result = Dt.AsEnumerable().Sum(x => Convert.ToInt32(x["Hr"]));

我也试过

also i tried

dt.Columns.Add("temp", typeof(int), "Convert(Hr, 'System.Int32')");
var str = dt.Compute("Sum(temp)", "Name='a' and Value is not null");

但不工作

推荐答案

请先阅读我对这个问题的评论。



你有两个方案可以解决你的问题：

Please, read my comment to the question first.

You have 2 options to resolve your issue:

1. 在SQL服务器端
   
   修改SQL语句以获得分隔字段中的小时和分钟
   

SELECT ID, [name], [FromDate], [ToDate], CAST(DATEDIFF(Minute,FromDate, ToDate) / 60 AS VARCHAR(5)) As 'Hrs',
    RIGHT('0' + CAST(DATEDIFF(Minute, FromDate, ToDate) % 60 AS VARCHAR(2)), 2) As 'Mins'
FROM records
WHERE <<criteria>>

之后，您将能够总结小时和分钟。

在客户端（使用Linq）

After that you'll be able to sum up hours and minutes.

on client side (using Linq)

DataTable dt1 = new DataTable();
dt1.Columns.Add(new DataColumn("Hr", typeof(string)));
dt1.Rows.Add(new object[]{"9 Hrs:05 Min"});
dt1.Rows.Add(new object[]{"0 Hrs:15 Min"});
dt1.Rows.Add(new object[]{"1 Hrs:23 Min"});
dt1.Rows.Add(new object[]{"3 Hrs:51 Min"});
dt1.Rows.Add(new object[]{"2 Hrs:32 Min"});
dt1.Rows.Add(new object[]{"4 Hrs:44 Min"});

var result = dt1.AsEnumerable()
	.Select(a=>a.Field<string>("Hr").Replace(" Hrs",  string.Empty).Replace(" Min", string.Empty))
	.Select(b=>new
		{
			Hrs = Convert.ToInt32(b.Split(new string[]{":"}, StringSplitOptions.RemoveEmptyEntries)[0]),
			Mins = Convert.ToInt32(b.Split(new string[]{":"}, StringSplitOptions.RemoveEmptyEntries)[1]),
		})
	.GroupBy(c=>1)
	.Select(d=>new
		{
			TotalHrs = d.Sum(x=>x.Hrs),
			TotalMins = d.Sum(x=>x.Mins)
		})
	.ToList();

以上代码将返回：

Above code will return:

TotalHrs TotalMins
19       170

如您所知，您必须将每个值60的分钟数除以能够将计算结果添加到小时，将其余部分添加到分钟。

祝你好运！

Good luck!

永远不要通过连接用户输入来构建SQL查询，它被命名为SQL注入，它对您的数据库很容易并且容易出错。

名称中的单引号和程序崩溃。如果像Brian O'Conner这样的用户输入可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability.
SQL injection - Wikipedia[^]
SQL Injection[^]

这篇关于如何显示小时和分钟的小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！