asked on

Get Elapsed Days Hours Minutes from datediff

I would like to create an elapsed time column that provides a result that includes days hours and minutes.

If the beginning time is 26 hours 30 minutes ago...the result would be 1, 2, 30.

Any help on how to go about this would be appreciated.

Guy Hengel [angelIII / a3]

what about this:
select datediff( day, date1, date2 ), datediff ( hour, date1, date2 ) , datediff ( minute, date1, date2 )

jamesh1031

ASKER

Well I think that would give me (from my example) total days, total hours and total minutes.....1, 26, and 1,590....individually and what I want is a result of 1, 2 and 30.

amr_bd

Hi jamesh,
How about this:

select
datediff(day, date1, date2) -- #of days
datediff (hour, date1, date2) - datediff(day, date1, date2)*24 -- #of hrs
datediff (minute, date1, date2) - datediff (hour, date1, date2)*60 -- #of mins
from
...

Thanks,
amr

ASKER CERTIFIED SOLUTION

amr_bd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

SELECT DATEDIFF(HOUR, beginTime, endTime) / 24 AS [Days],
DATEDIFF(MINUTE, beginTime, endTime) % 1440 / 60 AS [Hours],
DATEDIFF(SECOND, beginTime, endTime) % 3600 / 60 AS [Minutes]

For example:

DECLARE @beginTime DATETIME
DECLARE @endTime DATETIME
SET @endTime = GETDATE()
SET @beginTime = DATEADD(MINUTE, -30, DATEADD(HOUR, -26, @endTime)) --<<-- adjust as needed to test

SELECT DATEDIFF(HOUR, @beginTime, @endTime) / 24 AS [Days],
DATEDIFF(MINUTE, @beginTime, @endTime) % 1440 / 60 AS [Hours],
DATEDIFF(SECOND, @beginTime, @endTime) % 3600 / 60 AS [Minutes]