Post reply

Date Difference

  • May 24, 2005 at 4:02 pm

    #89262

    Hi all,

    How can I get the difference between two DATE columns! I tried DATEDIFF but it is giving either all hours or all minutes. I tried it to get the difference between      [ '2005-05-16 15:02:33.347'], [2005-05-17 09:23:30.813'] but the output was like 18hrs  or  1101 mins or  66057 secs. Can somebody help me out to get hours and mins;I dont care abt the secs.

  • May 24, 2005 at 4:59 pm

    #560873

    declare @x int

    declare @hr int

    declare @min-2 int

    select @x = datediff(mi,'2005-05-16 15:02:33.347', '2005-05-17 09:23:30.813')

    select @hr = @x / 60

    select @min-2 = @x % 60

    select @hr as 'hours', @min-2 as 'minutes'

    go

    You can only get one type of value back.  You have to do the math

    And then again, I might be wrong ...
    David Webb

  • May 25, 2005 at 2:15 am

    #560898

    If you can guarantee that the values will fall within the same day, then you could try

    select Convert(varchar(5),Cast('2005-05-16 16:02:33.347' as datetime)- Cast('2005-05-17 09:23:30.813'as datetime),108)

  • May 25, 2005 at 6:36 am

    #560921

    It's not quite true... you can get only 1 value at the time, but you can use datediff to get the hours and then recall it to get the minutes... Might run slower than David's solution though.

  • May 25, 2005 at 8:34 am

    #560979

    FWIW, another one:

    select

     convert(char(8),dateadd(ss,datediff(mi,'2005-05-16 15:02:33.347', '2005-05-17 09:23:30.813') ,0),108)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 25, 2005 at 8:36 am

    #560980

    Nice one Frank... it's nice to see you back here. Where have you been?

  • May 25, 2005 at 8:40 am

    #560982

    Thanks, I've never been away.

    Just reducing activity to work, learn and read.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 25, 2005 at 9:00 am

    #560990

    It worked me great........Thanks

  • May 25, 2005 at 9:01 am

    #560991

    I see Frank... Looks like I'm gonna get to 10k posts before you do at your current pace.

  • May 26, 2005 at 11:54 pm

    #561365

    Do you really want to bet on this?

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 27, 2005 at 6:37 am

    #561426

    Too much trouble to go to Europe to get your check...

  • May 27, 2005 at 6:43 am

    #561429

    Since I started my statistics, my avg. postcount daily is about ~8, since I record yours, yours is ~6,32. Guess when both counts will intersect

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 27, 2005 at 6:51 am

    #561435

    How about you recount that for the last month?

  • May 30, 2005 at 1:34 am

    #561654

    Ever had a beginners course in statistics? Results should be too volatile...

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 30, 2005 at 6:34 am

    #561680

    Don't need a stats course to understand this >>

    as of last Friday :

    Select 5222.0 / datediff(d, '12/5/2002', getdate()) as FRANK, 138 as POSTS_MAY_FRANK, 1369.0 / datediff(d, '5/10/2004', getdate()) as REMI, 368 as POSTS_MAY_REMI

    LifetTime_FRANK POSTS_MAY_FRANK LifetTime_REMI POSTS_MAY_REMI

    ------------------ --------------- ------------------ --------------

    5.757442116868 138 3.555844155844 368

    You got a better lifetime average but you're far off my current pace so it's actually possible that I catch up to you eventually.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply