Format DATEDIFF output

[Borgis]

I have two date/time values

26/08/2003 13:55
18/08/2003 14:00

I need to calculate the difference between them and format the result in days, hours, minutes e.g. the results from the select would be like below

Start End Days Hours Minutes
18/08/2003 14:00 26/08/2003 13:55 7 23 55

Any ideas?

Thanks in advance.

 

[Kolt]

so what's the problem with DATEDIFF?
use 'Convert' to get datetime value of 26/08/2003 13:55,
18/08/2003 14:00
and then DATEDIFF ( datepart , startdate , enddate )

 

[Borgis]

So exactly how would this fit into a select statement???

select date1, date2, DATEDIFF(datepart,date1,date2)
from table1

Don't think it would work, but I could be wrong

 

[TimKThailand]

Check this one out.

SET DATEFORMAT dmy --set Date format
GO
DECLARE @datestart datetime
DECLARE @dateend datetime
DECLARE @minutes int
DECLARE @days int 
SET @days = (24*60) -- one day has 24*60 mins
SET @datestart = '18/08/2003 14:00'
SET @dateend = '26/08/2003 13:55'
SET @minutes = DateDiff(minute,@datestart,@dateend)
select @minutes / @days As 'DAYS',
(@minutes % @days)/60 as 'HRS',
(@minutes % @days)%60 As 'MINS'
GO

 

[Borgis]

Thanks, but I was realy looking for a SELECT statement that could do it all in one go, e.g.

select (((DateDiff(ss, StartTime, EndTime) /60)/60)/24) as days, ((DateDiff(ss, StartTime, EndTime) /60)/60)%24 as hours, ROUND(((((((DateDiff(ss, StartTime, EndTime) /60.0)/60.0)/24.0)- (((DateDiff(ss, StartTime, EndTime) /60)/60)/24))*24) - ((((DateDiff(ss, StartTime, EndTime) /60)/60)%24))) * 60,0) as mins from testdates

 

[clarkin]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp

This is the Books On-Line entry for DATEDIFF. You can greatly simplify your above SELECT statement with something (or things) from that page. I'm not going to even start trying to make sense of all those brackets - just believe me when I say its WAY simpler.

If you're really stuck i'll give a hint

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[clarkin]

actually, on reading over your question as you want the 'remainder' hrs & mins its not as simple as it could be

TimKThailand has the best solution. You're probably on the right track with your big SELECT.

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].