c# - 返回大于 24 小时的 hh :mm:ss format in SQL server 2008

Question

我在 SQL 中有 View 名称为 vw_Rept_Attend 列名称为

 UserID (int),WorkHrs (varchar),ExtraHrs varchar(), AtnDate (datetime), EmpName (varchar), 
EmpType (varchar), UserName (varchar),Role (varchar)

实际工作在 WorkHrs 和 ExtraHrs

所以我正在做的是

我在 WorkHrs 中存储员工的每日工作时间，在 ExtraHrs 中存储加类时间，然后我显示通过过滤器提供的两个日期之间的出勤摘要报告，

很明显，我根据您的专家建议做了很多事情，应用程序在小数据上运行良好，在客户端测试中我遇到了一个我当时没有考虑的问题

问题描述

每位员工每天必须工作9小时(每日工作时间)

工作时间(登录时间)

加类时间(每日工作时间-工作时间)

如果员工工作 6 小时，那么他的加类时间将为 -3 小时，因为工作时间少于要求的时间，我通过这个 得到了这个结果c# 语句

wrktime= workinghrs.Subtract(Convert.ToDateTime(time).TimeOfDay);
TimeSpan Daily_work = new TimeSpan(9, 0, 0);
 TimeSpan exthrs = Convert.ToDateTime(wrktime).TimeOfDay.Subtract(Daily_work);

当任何人工作时间少于 9 小时

时，这将给我带来负面影响

因此，如果用户执行此操作 300 天，并且每天将 -3 小时 添加到 数据库，结果将为 -900小时 300 天

我使用以下sqlprocedure读取了所有这些记录以显示在 Crystal 报表上

ALTER PROCEDURE [dbo].[sp_Rpt_Emps_Attnd]
    @strt date,
    @end date
AS

    begin
    with cte
as
( select   UserID,
            WorkHrs,
         case when left(ExtraHrs,1)='-' then -1 else 1 end as multiply,
            right(ExtraHrs,8) as timestring,
            --get hours in seconds:
            DATEPART(HOUR,right(ExtraHrs,8)) * 3600 AS h_in_s,
            --get minutes in seconds:
            DATEPART(MINUTE,right(ExtraHrs,8)) * 60 AS m_in_s,
            --get seconds:
            DATEPART(SECOND,right(ExtraHrs,8)) AS s,AtnDate,EmpName,EmpType,
            UserName, Role,StartDate,EndDate

  from      vw_Rept_Attend
)

select   UserID,dbo.udfTimeSpanFromSeconds(Sum(Left(workhrs,2) * 3600 + substring(Convert(varchar(8),workhrs), 4,2) * 60 + substring(Convert(varchar(8),workhrs), 7,2))) as WorkHrs ,
case when sum((c.h_in_s + c.m_in_s + c.s) * multiply) < 0
            then '-' + CONVERT(varchar,DATEADD(s,ABS(sum((c.h_in_s + c.m_in_s + c.s) * multiply)),0),114) 

            else CONVERT(varchar,DATEADD(s,sum((c.h_in_s + c.m_in_s + c.s) * multiply),0),114)
        end as ExtraHrs ,EmpName,EmpType,UserName, Role,convert(VARCHAR(10),StartDate,105) as StartDate,convert(VARCHAR(10),EndDate,105) as EndDate

from    cte c  where  convert(date,AtnDate) between @strt and @end   
group by UserID,EmpName,EmpType,UserName, Role,StartDate,EndDate
  Order by UserID
    end

和用于计算日期之间所有WorkHrs总和的函数

    ALTER FUNCTION udfTimeSpanFromSeconds(
        @sec INT
    )
    RETURNS VARCHAR(15)
    AS
    BEGIN

RETURN 
    CONVERT(VARCHAR(10), (@sec / 3600)) + ':' +
    RIGHT('0' + CONVERT(VARCHAR(2), ((@sec % 3600) / 60)), 2) + ':' +
    RIGHT('0' + CONVERT(VARCHAR(2), (@sec % 60)), 2)
END

这是我从关于 stack 的问题中得到的 How to format time from dd:hh:mm:ss to only hh:mm:ss in SQL server?

但是计算 ExtHrs 总和的部分是我在上面提到的 存储过程 的一部分

case when left(ExtraHrs,1)='-' then -1 else 1 end as multiply,
            right(ExtraHrs,8) as timestring,
            --get hours in seconds:
            DATEPART(HOUR,right(ExtraHrs,8)) * 3600 AS h_in_s,
            --get minutes in seconds:
            DATEPART(MINUTE,right(ExtraHrs,8)) * 60 AS m_in_s,
            --get seconds:
            DATEPART(SECOND,right(ExtraHrs,8)) AS s

仅支持 24 小时例如，如果结果是 27 小时，则显示 3 小时，如果是 -56 小时 然后显示 -8 小时

我使用 SQL Server 2008,

我认为描述足以理解，但如果你们中的任何人不理解，请随意要求我分享代码或我使用的任何逻辑。

我们将不胜感激，

提前致谢。

编辑:

这个问题What is the correct SQL type to store a .Net Timespan with values > 24:00:00?完全不同，因为我不是在问我应该使用哪种 datatype

这里的应用程序可以获得负时间值，例如 -3 小时，我解释得很清楚，timespan 数据类型不接受负号。

现在我认为它应该清除

更新在我的数据库中它看起来像

所以经过计算 ExtHrs 应该是 -31:50:46 但它显示 -7:50:46

我认为这是因为不支持超过 24 小时

Answer 1

顺便说一句，proc 中存在许多技术缺陷以及您正在使用的数据类型。真正的问题就在这里。

1. 在您的过程中，您可以将 where 日期条件放在第一个 CTE 本身中。
2. 如果可能，您可以在表中以秒为单位存储 extrahours。这意味着 Extrahours 是 int 或 bigint。例如 -1000 或 1000。varchar 永远不会解决您的问题。它将使您免于大量转换，因此速度很快。
3. 在 group by 中使用这么多列本身就是错误的方法。特别是在 group by 中使用 varchar 列。您应该在 group by 中使用键列，然后再次与表连接以获得结果集中的其他列。

使用您的示例数据，我得到 -29:-51:-30.0 而不是 -31:50:46 。这样做，

DECLARE @t TABLE (ExtraHrs VARCHAR(20))

INSERT INTO @t
VALUES ('00:59:38')
    ,('-03:59:37')
    ,('-08:59:39')
    ,('-08:52:36')
    ,('-08:59:16');

WITH cte
AS (
    SELECT ExtraHrs
        ,CASE 
            WHEN left(ExtraHrs, 1) = '-'
                THEN - 1
            ELSE 1
            END AS multiply
        ,right(ExtraHrs, 8) AS timestring
        ,
        --get hours in seconds:
        DATEPART(HOUR, right(ExtraHrs, 8)) * 3600 AS h_in_s
        ,
        --get minutes in seconds:
        DATEPART(MINUTE, right(ExtraHrs, 8)) * 60 AS m_in_s
        ,
        --get seconds:
        DATEPART(SECOND, right(ExtraHrs, 8)) AS s
    FROM @t
    )
    ,CTE3
AS (
    SELECT *
        ,c.h_in_s + c.m_in_s + c.s AddExtra
    FROM cte c
    )
    ,cte4
AS (
    SELECT sum(AddExtra * multiply) mn
    FROM cte3
    )
    ,cte5
AS (
    SELECT mn / 3600 hh
        ,(mn % 3600) / 60 mi
        ,(mn % 3600.0) % 60 ss
    FROM cte4
    )
SELECT CASE 
        WHEN hh < 0
            THEN '-'
        ELSE ''
        END
    ,cast(hh AS VARCHAR) + ':' + cast(mi AS VARCHAR) + ':' + cast(ss AS VARCHAR)
FROM cte5

Try this for leading zero,

Declare @i int=5 SELECT REPLICATE('0',2-LEN(@i)) + cast(@i as varchar)