How do I calculate total minutes between start and end times?

Question

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.

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.

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

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:

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

Sample SQL Fiddle for the above queries.