Find time duration

Question

I have two date fields with times.They are

1.2008-06-09 10:18:00.000

2.2008-06-10 11:20:00.000

I have to find the difference between the above two dates in the format -

"24 hour:2 minutes:0 seconds"

Is there any method to get this result in sql 2000?

Answer 1

No fancy answer in SQL Server 2000...

DECLARE @d1 datetime, @d2 datetime 

SELECT @d1 = '2008-06-09 10:18:00.000', @d2 = '2008-06-10 11:20:00.000'

SELECT
    CAST(DATEDIFF(hour, @d1, @d2) AS varchar(30)) + ' hours, ' +
    CAST(DATEDIFF(minute, @d1, @d2) % 60 AS varchar(30))  + ' minutes, ' +
    CAST(DATEDIFF(second, @d1, @d2) % 3600 % 60 AS varchar(30))  + ' seconds'

The modulos are needed to remove hours and minutes already calculated

Answer 2

This is really nice question and i had also problem of such kind.. go to below link and you will find lost of such a date time functions.. If you find this link as a help full to you then please mark my answer as "Answer"

DateTime Functions