Datediff with a result like 26:32 (HH:MM) HOW ???

Question

Post reply

Datediff with a result like 26:32 (HH:MM) HOW ???

Jan Löfgren

SSC Enthusiast

Points: 197
More actions
June 17, 2004 at 6:42 am

#85764

Is there someone who can help me because i'm rather new to SQL (worked always in MS ACCESS) but what I want is the difference between 2 datetime fields expressed as HOURS:MINUTES (the hours can go over 24) and where I can do calculations on (Sum, average, etc). With DATEDIFF i get only an Integer in 1 expression (days or hour or minute, etc)
Example : 2004-05-11 00:00:00 - 2004-05-09 11:45:00 = 36:15
Thanks,
Jan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

declare @date1 datetime

declare @date2 datetime

select @date1 = '2004-05-11 00:00:00'

select @date2 = '2004-05-09 11:45:00'

select cast((cast(datediff(mi, @date2, @date1) as int) / 60) as varchar(5)) + ':' +

cast((select datediff(mi, @date2, @date1) - (cast(datediff(mi, @date2, @date1) as int) / 60) * 60) as varchar(2))

Beulah Kingsly SSCrazy Points: 2246 More actions · Answer 2

Allen's Query can be simplified as follows:

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

SELECT @date1 = '2004-05-11 00:00:00'

SELECT @date2 = '2004-05-09 11:45:00'

SELECT CAST((DATEDIFF(mi, @date2, @date1) / 60) AS VARCHAR(5)) + ':' + CAST((DATEDIFF(mi, @date2, @date1) %60) AS VARCHAR(2))

Regards,

Beulah Kingsly

rdg Mr or Mrs. 500 Points: 536 More actions · Answer 3

The query needs to be complicated slightly if you want to see a two digit figure after the colon when the time difference has less than 10 minutes:

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

SELECT @date1 = '2004-05-11 00:00:00'

SELECT @date2 = '2004-05-09 11:59:00'

SELECT

CAST((DATEDIFF(mi, @date2, @date1) / 60) AS VARCHAR(5))
+ ':' +
CASE
WHEN (DATEDIFF(mi, @date2, @date1) %60) < 10 THEN '0' + CAST((DATEDIFF(mi, @date2, @date1) %60) AS VARCHAR(2))
ELSE CAST((DATEDIFF(mi, @date2, @date1) %60) AS VARCHAR(2))
END

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 4

Hi,

There are tons of ways to this, here is another one...

DECLARE @minutes INT

SET @minutes = DATEDIFF(mi,'2004-05-09 11:45:00','2004-05-11 00:00:00')

-- Format minute interval to HH:MM

SELECT REPLACE(STR(@minutes/60,2,0)+':'+STR(@minutes%60,2,0),' ','0')

-- Format minute interval to HHHH:MM

SELECT REPLACE(STR(@minutes/60,4,0)+':'+STR(@minutes%60,2,0),' ','0')

/rockmoose

You must unlearn what You have learnt

Jan Löfgren SSC Enthusiast Points: 197 More actions · Answer 5

Guys,

thanks to all for the replies. I will try them out and let you know what the most refered solution is for me.

Jan

Mark Cohen Old Hand Points: 321 More actions · Answer 6

Grasshopper,

Just an "aside" question:

Instead of the case statement I would've done a RIGHT('00' + CAST((DATEDIFF(mi, @date2, @date1) %60) AS VARCHAR(2))

Is there a performance hit / reason why one is better than the other

Thanks

Mark

Mark Cohen

rdg Mr or Mrs. 500 Points: 536 More actions · Answer 7

Hi Mark

I guess it is just as rockmoose said, there are many ways to achieve this, and the CASE statement is the first one that came to mind! Nothing to do with performance issues on such a small calculation.

rdg