Geek Answers Handbook

Sum of DateTime difference in sql (HH.MM)

I have an SQL table like Where DateDiff in format (hh.mm)

DateDiff ATM 1.45 121 1.50 121 1.50 121

When I make Sum from DateDiff on Group by in ATM, it will show the result, for example

 4.45 121

But the actual time difference of the day should be

 5.25 121

How can we do the same in SQL Query

 Select Sum(Cast(Convert(Varchar(10),DATEDIFF(HOUR,TicketRaisedOn,ClosedOn))+'.'+ Convert(Varchar(10),DATEDIFF(MINUTE,TicketRaisedOn,ClosedOn)%60) as Numeric(18,2)))[Down Time],ATM From Ticket Where Closed=1 And DATEPART(DAYOFYEAR,GETDATE())=DATEPART(DAYOFYEAR,TicketRaisedOn) Group BY ATM Order By [Down Time] Desc

TicketRaisedOn and ClosedOn refer to DateTime

Database is SQL Server 2008

Above, the query will print the result this way (But its wrong, because it will sum it up as a number, not as a date format)

 Down Time ATM 16.95 282 14.46 1811 14.20 52 14.04 936

Data examples

 Select TicketRaisedOn,ClosedOn,ATM From Ticket Where ATM=282 And DATEPART(DAYOFYEAR,GETDATE())=DATEPART(DAYOFYEAR,TicketRaisedOn) And Closed=1 TicketRaisedOn ClosedOn ATM 2012-12-21 01:15:23.793 2012-12-21 15:11:59.240 282 2012-12-21 16:42:29.820 2012-12-21 18:21:30.797 282
+4
source share
2 answers

Perform Summation Before Formatting

 SELECT ATM, CONVERT(VARCHAR(10), SUM(DATEDIFF(Minute, TicketRaisedOn, ClosedOn)) / 60) + '.' + RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, TicketRaisedOn, ClosedOn)) % 60), 2) FROM Ticket GROUP BY ATM

Sql Fiddle: http://sqlfiddle.com/#!3/eca01/1

+2
source

What you do does not work, because 1.5 is an hour and a half, not one hour and 50 minutes.

To get what you want, do arithmetic in a small block, such as seconds, then convert it to datetime, and then datetime to the final representation. Here is an example:

 select right(convert(varchar(255), DATEADD(second, DATEDIFF(s, TicketRaisedOn,ClosedOn), cast(0 as datetime)), 120), 8)
+3
source

More articles:


All Articles