在SQL Server 2008中添加时间

Question

我想在SQL Server中添加时间。我在数据库中有一个叫做checktime的列，它是datetime数据类型。我想加上checktime列的时间。我该怎么做??

我正在做这个计算来计算员工在指定的一天内工作的总小时数。

我的数据库是这样的…我想把checktype = 1和checktype = 2中checktime的时间加起来，然后减去结果。(checktype = 1表示签入，checktype = 2表示签出)

我该怎么做??

  Id        EmpId              CheckTime                                CheckType
---------------------------------------------------------------------------------
  3           5             2013-01-03 09:00:15.000                         1 
  4           5             2013-01-03 11:00:00.000                         2 
  5           5             2013-01-03 11:30:00.000                         1 
  6           5             2013-01-03 13:00:00.000                         2 
  7           5             2013-01-03 13:30:00.000                         1 
  8           5             2013-01-03 16:00:00.000                         2 
  9           5             2013-01-03 16:30:00.000                         1 
 10           5             2013-01-03 18:00:00.000                         2 

Answer 1

如果我正确理解了你的问题，我会这样做:

SELECT
    EmpId,
    CheckTime,
    CheckType,
    ROW_NUMBER() OVER(PARTITION BY EmpId, DATEADD(dd,DATEDIFF(dd,0,cin.CheckTime),0), CheckType ORDER BY CheckTime) AS Seq
INTO
    #PreparedTable
FROM
    SourceTable

SELECT
    cin.EmpId,
    DATEADD(dd,DATEDIFF(dd,0,cin.CheckTime),0) AS CheckDate,
    (SUM(DATEDIFF(ss,cin.CheckTime,cout.CheckTime)) / 3600.0) AS HoursWorked
FROM
    #PreparedTable cin
JOIN
    #PreparedTable cout
    ON  (cin.EmpId = cout.EmpID)
    AND (DATEDIFF(dd,cin.CheckTime,cout.CheckTime) = 0)
    AND (cin.Seq = cout.Seq)
    AND (cin.CheckType = 1)
    AND (cout.CheckType = 2)