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

How do I calculate total minutes between start and end times? The Start/End times columns are nvarchar and I am declaring them as datetime. I'm not sure if that is my first step or not, I am new to SQL and to declaring.

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

The final goal is to take Total Minutes, subtract Lunch and Recess (both are minutes) and then multiply by 5 to get total instructional minutes for the week per school.

最终目标是采取总分钟数,减去午餐和休息时间(均为分钟数),然后乘以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

2 个解决方案

#1

Maybe something like this is what you want?

也许这样的东西是你想要的?

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

If you want to sum it up for all rows then try:

如果要对所有行进行总结,请尝试:

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

If you want to get the number of hours per school you would have to include the schoolfield in the query and use it in the group byclause, and then the query becomes this:

如果你想获得每个学校的小时数,你必须在查询中包含schoolfield并在group byclause中使用它,然后查询变为:

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

Sample SQL Fiddle for the above queries.

上述查询的示例SQL小提琴。

#2

If all you want is to find the difference between two dates then you can use DATEDIFF function (http://msdn.microsoft.com/en-us/library/ms189794.aspx)

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

Example:

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);

If however your values are in string format you need to convert them prior to passing them to the DATEDIFF function. Example:

但是,如果您的值是字符串格式,则需要在将它们传递给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);

#1