How do I calculate the total minutes between start and end times?
How do I calculate the total minutes between start and end times? The Start / End times columns are nvarchar and I declare them as datetime. I'm not sure if this is my first step or not, I'm new to SQL and declare.
The end goal is to take Total Minutes
, subtract, Lunch
and Recess
(both minutes) and then multiply by 5 to get the total weekly minutes of 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
Perhaps something like this you need?
select (datediff(minute, starttime, endtime) -lunch -recess) * 5 AS TotalInstruct
from YourTable
If you want to summarize for all lines, try:
select sum((datediff(minute, starttime, endtime) -lunch -recess) * 5) AS TotalInstruct
from YourTable
If you want to get the number of hours in school, you will need to specify a field school
in the request and use it in the proposal group by
, and then the request becomes as follows:
select school, sum((datediff(minute, starttime, endtime) -lunch -recess) * 5) AS TotalInstruct
from YourTable
group by school
SQL Fiddle example for the above queries.
If you want to find the difference between two dates, you can use the 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 your values are in string format, you need to convert them before 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);