Adding date difference of two dates in Sql for all columns
I have a table with two date columns as ArrivalDate and DepartureDate. I need to calculate the total time period in hours and minutes (not date) of all the entries in the table . I can get the time period of a particular record via datediff but what i need is the sum of all the date differences in my table in hours and minutes .
What I am doing is this for getting date difference
set @StartDate = '10/01/2012 08:40:18.000' set @EndDate = '10/04/2012 09:52:48.000' SELECT CONVERT(CHAR(8), CAST(CONVERT(varchar(23),@EndDate,121) AS DATETIME) -CAST(CONVERT(varchar(23),@StartDate,121)AS DATETIME),8) AS TimeDiff
This only gives a particular date difference . I need all the date differences in my table and then there sum.
This query will generate the results you are looking for:
SELECT SUM(DATEDIFF(minute, R.ArrivalDate, R.DepartureDate)) / 60 as TotalHours , SUM(DATEDIFF(minute, R.ArrivalDate, R.DepartureDate)) % 60 as TotalMinutes FROM TableWithDates R
The first column returned divides the total minutes by 60 to give you the whole number of hours. The second column returned calculates the remainder when dividing the total minutes by 60 to give you the additional minutes remaining. Combined the 2 columns give you the sum total of the elapsed hours and minutes between all of your Arrival and Departure dates.
SELECT SUM(DATEDIFF(HOUR, t.EndDate, t.StartDate)) AS Hours, ( SUM(DATEDIFF(MINUTE, t.EndDate, t.StartDate)) - SUM(DATEDIFF(HOUR, t.EndDate, t.StartDate)) * 60 ) AS Minutes FROM YOUR_TABLE t
IT should do the trick.
You can try something like this..
SELECT CAST( (SUM(DATEDIFF(mi , ARRIVALDATE , DEPARTUREDATE)))/60 AS VARCHAR(10)) + ':'+ CAST( (SUM(DATEDIFF(mi , ARRIVALDATE , DEPARTUREDATE)))%60 AS VARCHAR(10)) FROM YOURTABLE
this is just a pseudo code.. replace table and column names as needed..
If you have long time periods int will fail in SUM. So this will work,
SELECT TIME / 60 AS HOURS ,TIME % 60 AS MINUTES FROM ( SELECT SUM(CAST(DATEDIFF(MINUTE, ArrivalDate, DepartureDate) AS BIGINT)) AS Time FROM Table ) A
Declare @totalSeconds as numeric SELECT @totalSeconds = sum(Datediff(ss, ArrivalDate , DepartureDate)) from tableName SELECT @totalSeconds //displays total seconds select CAST(@totalSeconds /3600 AS int), (CAST(@totalSeconds /60 AS int) - (CAST(@totalSeconds /3600 AS int)*60)) //converting seconds to hours & minutes
I tried following
DECLARE @TotalWaitingMinutesOverall INT = (SELECT SUM(DATEPART(MI, (ISNULL(DepartureTime, GETDATE()) - ArrivalTime))) FROM MY TABLE
Then, @Hrs INT = TotalWaitingMinutesOverall /60
Then to display the time,
SELECT CONVERT(NVARCHAR(10), @Hrs) + ':' + CONVERT(NVARCHAR(10), @TotalWaitingMinutes - (@Hrs * 60)) AS TIME