CODEWITHSUNDEEP
sqlsql-server
C.Sher
C.Sher

Reputation: 23

SQL. Convert datediff hours to dd.hh:mm:ss

I've got some code that generates hours between an oldest record in the table (MIN) and getdate(), and am using 

CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,MIN(OLDESTRECORD),GETDATE()),0),108)

to get the HH:MM:SS, but when the hours is more than 24 hours it just shows the hours part. i.e. 30 hours difference shows as 6 hours... how do I get it to show dd.hh:mm:ss i.e. 1.06:00:00

Upvotes: 2

Views: 1950

Answers (1)

Cato
Cato

Reputation: 3701

watch out for the case where the 'time' in the start day has not been reached in the end date

 select cast(DATEDIFF(SECOND,MIN(OLDESTRECORD),GETDATE()) /  86400  as varchar(4)) + '.' +  CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,MIN(OLDESTRECORD),GETDATE()) % 86400 ,0),108)

Upvotes: 3

Related Questions