# 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.

## Answers

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.

Try this:

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

Try this

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