none
Get Difference between two dates as Hours, Minutes

    Question

  • Hi,

     I am using the below query to get difference beiween two dates in hours and minutes.Minutes part look ok but the hours is incorrect.

     select datediff(minute,LastDate,getdate()) as "Minutes", datediff(hour,LastDate,getdate()) as "Hours"from emp.

     Note: Using SQL Server 2005


    Regards
    Janet

    Thursday, September 23, 2010 2:21 PM

Answers

  • Then Calculate no of minutes then divide it by 60..

    select datediff(minute,@date,getdate())/60
    
    • Marked as answer by janets20 Thursday, September 23, 2010 4:18 PM
    Thursday, September 23, 2010 2:23 PM

All replies

  • Why is that? Can you provide some test data?
    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 23, 2010 2:22 PM
  • Then Calculate no of minutes then divide it by 60..

    select datediff(minute,@date,getdate())/60
    
    • Marked as answer by janets20 Thursday, September 23, 2010 4:18 PM
    Thursday, September 23, 2010 2:23 PM
  • Then Calculate no of minutes then divide it by 60..

    select datediff(minute,@date,getdate())/60
    

    Rami,

    this solution is like telling the OP there is an issue in the code she posted, yet in fact it might be a data problem.

    I suggest we look at the data first and try to find out the root cause of the issue instead of providing an alternative solution.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 23, 2010 2:27 PM
  • Yes Abdshall,

      Sorry for that....     Some days back i heard about Whole Number thing, which causes this behaviour... But i think its for days...I am not sure..

    Although, I am not able to reproduce the same scenario for hours here, I thought its at some point of time.... so, i didn't ask for re-producing...instead i gave an workaround..........

     

    Thursday, September 23, 2010 2:30 PM
  • I am talking about this....

    declare @date datetime = '9/23/2010 07:53:59'
    declare @date1 datetime = '9/23/2010 07:59:59' 
    declare @date2 datetime = '9/23/2010 08:00:00' 
    select DATEDIFF(hour,@date,@date1),DATEDIFF(hour,@date,@date2)
    Thursday, September 23, 2010 2:33 PM
  • Rami,

    I see what you are saying. That's why when the exact time is calculated, for example HH:MM:SS, it not calculated using DATEDIFF function only depending on hour,minutes, and seconds. It is done in a different way, and if thats what the OP wants, here is how she can do that.

    DECLARE @mintime DATETIME = '2009-02-02 11:12:20.123'
    DECLARE @maxtime DATETIME = '2009-02-02 13:30:25.123'
    
    declare @duration int
    set @duration = datediff([second], @minTime, @maxtime);
    
    SELECT CASE WHEN LEN(H) = 1
    		THEN '0' + h
    		ELSE h END
    		+ ':' +
    		CASE WHEN LEN(m) = 1
    		THEN '0' + m
    		ELSE m END
    		+ ':' + 
    		CASE WHEN LEN(s) = 1
    		THEN '0' + s
    		ELSE s END
    		FROM
    		(
    select
      CAST(@duration / 3600 AS VARCHAR(3)) as [h],
      CAST(@duration % 3600 / 60 AS VARCHAR(3)) as [m],
      CAST(@duration % 3600 % 60 AS VARCHAR(3))as [s]) AS X
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 23, 2010 2:50 PM