CODEWITHSUNDEEP
sql-server
Sarwar Hussain
Sarwar Hussain

Reputation: 21

Calculate date between two date and sum then convert into hours and minutes

I want to get the Date difference in hours and minutes and to achieve that I have used this query in SQL Server:

Cast(SUM(DATEDIFF(second, fromtime, totime) / 60 / 60 % 24) as varchar(20)) + ' hours '
+CAST(SUM(DATEDIFF(second, fromtime, totime) / 60 % 60)as varchar(20))+' minutes '  as [TotalTime]

It returns the following Output:

TotalTime
---------------------
16 Hours 120 Minutes 
3 Hours 90 Minutes

but the expected output is:

TotalTime
----------------------
18 Hours 0 Minutes 
4 Hours 30 Minutes.

Can anyone help me to solve this issue?

Upvotes: 1

Views: 666

Answers (1)

knkarthick24
knkarthick24

Reputation: 3216

Your query is perfect, All you need to do is to write a outer query over it. Something like below

select case when t.minutes>60 then t.hours+(t.minutes/60) else t.hours end as hours,
       case when t.minutes>60 then t.minutes%60 else t.minutes end as minutes
from
(
select 
Cast(SUM(DATEDIFF(second, fromtime, totime) / 60 / 60 % 24) as varchar(20)) as hours
+CAST(SUM(DATEDIFF(second, fromtime, totime) / 60 % 60)as varchar(20)) as minutes
from tablename
)as t

Upvotes: 1

Related Questions