从数据集中获得HH:MM格式的总数[英] Get total in HH:MM format from the dataset

本文是小编为大家收集整理的关于从数据集中获得HH:MM格式的总数的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

CREATE procedure St_Proc_GetUserReportforCurrentDayTask                        
@userID int                        
as                        
    Begin                        
        set NoCount on;                        
        DECLARE @TODAY DATE                          
        SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111)                        
        select  CONVERT(VARCHAR,production.CalendarDate,101) + RIGHT (CONVERT(VARCHAR,production.CalendarDate , 100 ) ,7) as Date,                         
        RegionAndProjectInfo.RegionProjectName as Region ,                        
        County.CountyName as County,                        
        WorkType.WorkTypeName as WorkType,                        
        Task.TaskName as Task,            
        Production.VolumeProcessed as 'Volumes Processed',                        
        Production.TimeSpent as 'Duration'                        
        from Production                         
        inner join RegionAndProjectInfo                        
        on                        
        RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID                        
        inner join County                        
        on                         
        County.CountyID=Production.CountyID                        
        inner join WorkType                        
        on                        
        WorkType.WorkTypeID=Production.WorkTypeID                        
        inner join Task                        
        on                        
        Task.TaskID=Production.TaskID                        
        where Production.UserID=@userID and CalendarDate >= @TODAY                        
    End 

从上面存储的过程中,我正在填充数据集.在将该数据集绑定到网格视图之后. 在数据集中,列持续时间包含HH:MM格式的数据(示例-01:00,12:45,02:59 etc).有一种方法可以从HH:MM格式中获得总持续时间数据集本身.我不想从数据库中再次查询以获取持续时间的总和.

推荐答案

如果您至少使用SQL-Server 2008,则应使用Time数据类型来表示.NET TimeSpan.

要与varchar-lumn一起使用,我将使用Linq-To-DataSet,例如:

var timeSpans = dataSet.Tables[0].AsEnumerable()
    .Select(r => new {
        DurHour   = r.Field<String>("Duration").Split(':')[0],
        DurMinute = r.Field<String>("Duration").Split(':')[1]
    })
    .Select(x => new TimeSpan(int.Parse(x.DurHour), int.Parse(x.DurMinute), 0));

Console.WriteLine("Total time in minutes: {0}", timeSpans.Sum(ts=>ts.TotalMinutes));

但这只是一个解决方法,实际上,您应该避免在Varchar列中存储一个时间pan,仅将varchar使用用于文本而没有其他内容.

在SQL-Server 2005(或其他DBMS)中,您也可以使用两列,一列用于DateTime,另一个用于DateTime.

.

本文地址:https://www.itbaoku.cn/post/260921.html

问题描述

CREATE procedure St_Proc_GetUserReportforCurrentDayTask                        
@userID int                        
as                        
    Begin                        
        set NoCount on;                        
        DECLARE @TODAY DATE                          
        SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111)                        
        select  CONVERT(VARCHAR,production.CalendarDate,101) + RIGHT (CONVERT(VARCHAR,production.CalendarDate , 100 ) ,7) as Date,                         
        RegionAndProjectInfo.RegionProjectName as Region ,                        
        County.CountyName as County,                        
        WorkType.WorkTypeName as WorkType,                        
        Task.TaskName as Task,            
        Production.VolumeProcessed as 'Volumes Processed',                        
        Production.TimeSpent as 'Duration'                        
        from Production                         
        inner join RegionAndProjectInfo                        
        on                        
        RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID                        
        inner join County                        
        on                         
        County.CountyID=Production.CountyID                        
        inner join WorkType                        
        on                        
        WorkType.WorkTypeID=Production.WorkTypeID                        
        inner join Task                        
        on                        
        Task.TaskID=Production.TaskID                        
        where Production.UserID=@userID and CalendarDate >= @TODAY                        
    End 

From the above Stored Procedure i am filling a Dataset.After that i am binding this Dataset to a grid view. In the dataset ,the Column Duration contains Data in HH:MM Format(example-01:00,12:45,02:59 etc).Is there a way that i can get the total of Duration in HH:MM format from the dataset itself.I dont want to query again from the Database to get the Sum of the Duration.

推荐答案

If you're using at least SQL-Server 2008, you should use Time datatype to represent a .NET TimeSpan.

To get it to work with a varchar-column, i would use Linq-To-DataSet, for example:

var timeSpans = dataSet.Tables[0].AsEnumerable()
    .Select(r => new {
        DurHour   = r.Field<String>("Duration").Split(':')[0],
        DurMinute = r.Field<String>("Duration").Split(':')[1]
    })
    .Select(x => new TimeSpan(int.Parse(x.DurHour), int.Parse(x.DurMinute), 0));

Console.WriteLine("Total time in minutes: {0}", timeSpans.Sum(ts=>ts.TotalMinutes));

But that is only a workaround, actually you should really avoid to store a timespan in a varchar column, use varchar only for text and nothing else.

In SQL-Server 2005 (or other dbms) you could also use two columns, one for the start DateTime and one for the end DateTime.