Results 1 to 5 of 5
  1. #1
    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. #2
    Join Date
    Dec 2004
    Posts
    46
    you mean dateadd?

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

  3. #3
    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. #4
    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. #5
    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
  •