Given two date/times:
@start_date = '2009-04-15 10:24:00.000'
@end_date = '2009-04-16 19:43:01.000'
Is it possible to calculate the time elapsed between the two dates in the following format
1d 9h 19m
views:
1402answers:
4
+6
A:
You can get the difference between the two dates to whatever resolution you want (in your example, minutes):
DATEDIFF(minute, @start_date, @end_date)
From there it's a simple matter of dividing minutes into hours and hours into days and modding the remainder.
Rex M
2009-04-17 03:26:57
I can see how DATEDIFF might work but it always returns TOTAL time. I can get total days or total hours but I can't get the span 1day 9hours. At least not in a straight forward way. Example?
jdiaz
2009-04-17 03:53:31
@jdiaz as stated in the answer, you need to get the total at the level of precision you need. If you need precision to minutes then get it in minutes, then divide by 60 to get the hours (remainder is minutes), then divide the hours by 24 to get days (remainder is hours).
Rex M
2009-04-17 04:12:55
+1
A:
datediff(datepart, date1, date2);
Rex's answer is more complete, upmodding him.
tpdi
2009-04-17 03:27:11
A:
DATEDIFF can return unintuitive values. For example, the two dates below differ by one second yet DATEDIFF with the parameters below and interpreted as others have interpreted it above returns 1 year:
SELECT DATEDIFF(year, '2005-12-31 23:59:59', '2006-01-01 00:00:00')
Look at the MSDN documentation for DATEDIFF to understand how it works.
Paul S...
2010-06-15 00:37:23
A:
CONVERT(varchar,(@end_date-@start_date),108)
This'll give it to you as HH:MM:SS
Cheers
Dom
2010-08-03 09:31:01