CODEWITHSUNDEEP
sql-servert-sql
JM1
JM1

Reputation: 1715

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

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

Upvotes: 20

Views: 51424

Answers (2)

jpw
jpw

Reputation: 44881

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.

Upvotes: 29

Lee
Lee

Reputation: 584

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)

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:

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

Upvotes: 4

Related Questions