Post reply

Calculate the Hour and Time between two dates

  • February 26, 2008 at 5:32 pm

    #187120

    can any one tell me the calculation of hour and second between two dates

    here is the senario

    StartDate = '2008-02-26 01:40:08.000'

    EndDate = '2008-02-26 01:49:21.000'

    Now i am looking for total time different between this dates

    so it should be 9 Minute and 13 Second.

    I am looking for Ans formate as 9:13.

    Help me

  • February 26, 2008 at 5:44 pm

    #782313

    Use the datediff function, and store the difference in a datetime field. Use the convert to show what you want to see

    declare @startdate datetime

    declare @EndDate datetime

    set @StartDate = '2008-02-26 01:40:08.000';

    set @EndDate = '2008-02-26 01:49:21.000';

    select convert(char,dateadd(second,datediff(second,@startdate,@enddate),0),108)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 26, 2008 at 5:57 pm

    #782315

    I had a little different approach. Either way.

    declare @startdate datetime,

    @enddate datetime,

    @sec int

    set @StartDate = '2008-02-26 01:40:08.000'

    set @EndDate = '2008-02-26 01:49:21.000'

    set @sec = datediff(s,@startdate,@enddate)

    select

    floor(@sec/60)-floor(@sec/60/60)*60 as Minutes ,

    @sec-floor(@sec/60)*60 as Seconds


    My blog: http://jahaines.blogspot.com

  • February 26, 2008 at 7:11 pm

    #782327

    Just a different approach... note that this is only good if within 60 minutes as the OP seemed to indicate wanting... (09:13)...

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    SET @StartDate = '2008-02-26 01:40:08.000'

    SET @EndDate = '2008-02-26 01:49:21.000'

    SELECT RIGHT(CONVERT(CHAR(8),@EndDate-@StartDate,108),5)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 26, 2008 at 11:36 pm

    #782364

    Thank you all, it helps lot

Viewing 5 posts - 1 through 4 (of 4 total)

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