# Thread: Datediff with more than 24 hours

1. Registered User
Join Date
Dec 2004
Posts
3

## Unanswered: Datediff with more than 24 hours

Hi Everybody!
I'm trying to perform a query which will
bring me a datediff between two collumns in a table.
When the result have more than 24 hours
it starts to count from 0 again and i need to show a result
like this "27:30:45" and not "03:30:45"
How can i show more than 24 hours including minutes and seconds as a result?
Thanks in advance.
Guto
Last edited by gutomore; 01-07-05 at 08:57.

2. Registered User
Join Date
Dec 2004
Posts
46
you mean dateadd?

select convert(varchar,datepart(hour, xxx) + 24) + ":" .....

3. Registered User
Join Date
Dec 2004
Posts
3

## datediff more than 24 hours

Hi there,
i really mean datediff !!!
like this : Final_datetime - Initial_datetime = Interval in hh:mm:ss )
Tks.

4. Registered User
Join Date
Dec 2004
Posts
46
have a look at this:

declare @a datetime, @b datetime

select @a = '2004-12-27 21:37:19'
select @b = '2004-12-29 12:14:46'

select @b - @a
select convert(varchar, @b - @a, 8)
select convert(varchar, datediff(hour, '1900-01-01', @b - @a)) + substring(convert(varchar, @b - @a, 8), 3, 999)

I bet u use convert to get hh:mm:ss but infact @b-@a gives a datetime.

5. Registered User
Join Date
Dec 2004
Posts
25
Try this :

declare @a datetime, @b datetime
select @a = '2004-12-27 21:37:19'
select @b = '2005-12-27 22:49:18'

select right('0'+convert(varchar(4),datediff(hh,@a,@b)),2 )+':'+right('0'+convert(varchar(2),(datediff(mi,@a ,@b) - (datediff(hh,@a,@b)*60))),2)+':'+right('0'+ convert(varchar(2),(datediff(ss,@a,@b) - (datediff(mi,@a,@b)*60))),2)

This will work provided b > a
Last edited by xmwolverine; 01-13-05 at 10:25.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•