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
source to share
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.
source to share
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);
source to share