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


Need Your Help

Unit testing Angular with Breeze

javascript angularjs unit-testing breeze qunit

I am trying to unit test angularjs with QUnit but get the error messages: $httpBackend.whenGET is not a function, $httpBackend.when is not a function. I have included angular mocks and angular breeze

Override the google custom search css

search google-custom-search

In my site have google custom search. I have an issue with the font of the search result. I need Arial,sans-serif .