DateDiff Function in SQL Server

Question

I have a query

SELECT  CONVERT(VARCHAR(8), DATEADD(SS, DATEDIFF(SS, '2017/08/24 06:00', '2017/08/25 07:15'), 0), 114)

which gives output as 01:15:00, where the date is not considered. My expected output is 25:15:00 because the difference of the day is 1. I tried with replacing SS with HH but did not get the desired help.

Take a look at this question:(https://stackoverflow.com/questions/758891/calculating-timespan-with-t-sql) — Brett, Sep 26 '17 at 08:17
@YogeshSharma - Yes, I want time difference by considering date also.. In above case time diff is 25+ hours — rinuthomaz, Sep 26 '17 at 08:21
have you try using cast (date1-date2 as time(0)) wht output it gives ? — Yogesh Sharma, Sep 26 '17 at 08:26
It return 1H because you have more than 24h, the problem not in datediff, it's on convert. — Ilyes, Sep 26 '17 at 08:27
I tried this 'CONVERT(varchar(8), DATEADD(ms, DATEDIFF(mi, '2017/08/24 06:00', '2017/08/25 07:15') * 1000, 0), 114)' - 25 shows as minutes — rinuthomaz, Sep 26 '17 at 08:31

score 3 · Answer 1 · answered Sep 26 '17 at 08:31

3

with cte as
(
select DATEDIFF(SS, '2017/08/24 06:00', '2017/08/25 07:15') as ss
)

select cast (ss/3600 as varchar(10)) +':' + right( '00' +cast(ss % 3600 / 60 as varchar(2)), 2) + ':' + right( '00' + cast( (ss % 3600 )% 60 as varchar(2)), 2)
from cte

answered Sep 26 '17 at 08:31

sepupic

8,409
1
9
20

1

I like this answer more than the answers in the duplicate questions. – GuidoG Sep 26 '17 at 08:38

DateDiff Function in SQL Server

1 Answers1