none
Difference between time periods RRS feed

  • Question

  • Hi, I am trying to get the difference between StartTime and EndTime of a session.

    SELECT DATEDIFF(mi, '08:55', '11:00')/60;

    The above query returns 2 hours, but I am looking to output 2 hours & 5 mins. How do I do my calculation to get the difference in Hours:Minutes. I am using SQL Server 2008 R2.

    Thanks in advance............

    Tuesday, December 11, 2012 6:37 PM

Answers

  • In this case

    SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60.0 as decimal(10,3)) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 11, 2012 6:59 PM
    Moderator

All replies

  • Try

    SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60 as varchar(10)) + ':' + 
    RIGHT('00'+CAST(DATEDIFF(mi, '08:55', '11:00')%60 as varchar(10)),2)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 11, 2012 6:40 PM
    Moderator
  • Hi Naomi, Thanks for your response. I actually want 2 hours & 5 mins as 2.083. Apologize for not mentioning it on the original post.
    Tuesday, December 11, 2012 6:53 PM
  • SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60. as decimal(6,3))

    Tuesday, December 11, 2012 6:58 PM
    Moderator
  • In this case

    SELECT CAST(DATEDIFF(mi, '08:55', '11:00')/60.0 as decimal(10,3)) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 11, 2012 6:59 PM
    Moderator
  • try this...

    -- Build some sample data
    DECLARE @Ranges TABLE (
    	Start DateTime,
    	Finish DateTime)
    INSERT INTO @Ranges VALUES
    	('20110101 05:05:05.000','20110108 06:06:06.000'),
    	('20110101 05:05:05.000','20110108 07:07:07.000'),
    	('20110101 05:05:05.000','20110110 09:09:09.000'),
    	('20110101 05:05:05.000','20110114 10:11:12.000'),
    	('20110101 05:05:05.000','20110116 23:54:45.000'),
    	('20110101 05:05:05.000','20110122 17:18:16.000')
    -- The actual solution
    SELECT 
    CAST(DATEDIFF(ss, Start, Finish) / 86400 AS INT) AS [Days],
    CAST(DATEDIFF(ss, Start, Finish) % 86400 / 3600 AS INT) AS [Hours],
    CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 / 60 AS INT) AS [Minutes],
    DATEDIFF(ss, Start, Finish) % 86400 % 3600 % 60 AS [Seconds],
    CAST(CAST(DATEDIFF(ss, Start, Finish) / 86400 AS INT) AS VARCHAR(5)) + ' Days, ' +
    CAST(CAST(DATEDIFF(ss, Start, Finish) % 86400 / 3600 AS INT) AS VARCHAR(5)) + ' Hours, ' +
    CAST(CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 / 60 AS INT) AS VARCHAR(5)) + ' Mins, ' +
    CAST(DATEDIFF(ss, Start, Finish) % 86400 % 3600 % 60 AS VARCHAR(5)) + 'Sconds' AS AmtOfTime
    FROM @Ranges

    HTH,

    Jason


    Jason Long

    Tuesday, December 11, 2012 7:00 PM
  • Thanks all that worked. I ended up using Naomi's solution.
    Tuesday, December 11, 2012 7:07 PM