如何计算开始时间和结束时间之间的总分钟数？

Question

如何计算开始时间和结束时间之间的总分钟数？开始/结束时间列是nvarchar，我将它们声明为datetime。我不确定这是否是我的第一步，我是SQL和声明的新手。

最终目标是用Total Minutes减去Lunch和Recess (都是分钟)，然后乘以5，得到每个学校一周的总教学分钟数。

DECLARE @StartTime datetime,  @Endtime datetime

SELECT --[School]
      [GradeLevel]
      ,[StartTime]
      ,[EndTime]
      ,(@Endtime - @StartTime) AS 'TotalMinutes'
      ,[Lunch]
      ,[Resess]
      ,[Passing]
  FROM [dbo].[StartEndTimes]


Current Output:
GradeLevel  StartTime   EndTime   TotalMinutes    Lunch   Resess    Passing
 2-5         7:50        14:20      NULL            20      10       NULL
 K-5         7:45        14:20      NULL            20      10       NULL
 K-5         7:50        14:20      NULL            20      10       NULL

Answer 1

也许这样的东西就是你想要的？

select (datediff(minute, starttime, endtime) -lunch -recess) * 5 AS TotalInstruct
from YourTable

如果您想对所有行进行求和，请尝试：

select sum((datediff(minute, starttime, endtime) -lunch -recess) * 5) AS TotalInstruct
from YourTable

如果想要获取每个学校的学时数，则必须在查询中包含school字段，并在group by子句中使用它，然后查询将变为：

select school, sum((datediff(minute, starttime, endtime) -lunch -recess) * 5) AS TotalInstruct
from YourTable
group by school

以上查询的Sample SQL Fiddle。

Answer 2

如果您只想找出两个日期之间的差异，那么可以使用DATEDIFF函数(http://msdn.microsoft.com/en-us/library/ms189794.aspx)

示例：

DECLARE @startdate datetime2
SET @startdate = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722'; 
SELECT DATEDIFF(MINUTE, @enddate, @startdate);

但是，如果您的值是字符串格式，则需要在将它们传递给DATEDIFF函数之前对其进行转换。示例：

DECLARE @starttexttime nvarchar(100)
SET @starttexttime = '7:50'
DECLARE @starttime datetime2
SET @starttime = CONVERT(datetime2, @starttexttime, 0)

DECLARE @endtexttime nvarchar(100)
SET @endtexttime = '17:50'
DECLARE @endtime datetime2
SET @endtime = CONVERT(datetime2, @endtexttime, 0)

SELECT DATEDIFF(MINUTE, @starttime, @endtime);