locked
Datetime Difference RRS feed

  • Question

  • Hi all,

    I have a rather basic question, but
    having difficulty.... I need to calculate the difference between 2 times. Any
    help would be appreciated.

    Example:

    09:28:00 - 09:38:00 =
    00:10:00

    Dave SQL Developer

    Friday, September 6, 2013 6:35 PM

Answers

  • SELECT CAST(DATEDIFF(s, Start, End) / 3600 AS VARCHAR(2)) + ':' + CAST((DATEDIFF(s, Start, End) / 60) % 60 AS VARCHAR(2)) + ':' + CAST(DATEDIFF(s, Start, End) % 60 AS VARCHAR(2))


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Marked as answer by DaveDB Friday, September 6, 2013 7:32 PM
    Friday, September 6, 2013 7:29 PM

All replies

  • please see datediff function

    http://technet.microsoft.com/en-us/library/ms189794.aspx

    http://matijabozicevic.com/blog/sql-server-development/sql-calculating-time-difference-in-minutes-hours-days-weeks-months-years

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Friday, September 6, 2013 6:41 PM
    Friday, September 6, 2013 6:36 PM
  • I am not sure how to code it, can you please help?

    Dave SQL Developer

    Friday, September 6, 2013 6:41 PM
  • pls see the 2nd link provided

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, September 6, 2013 6:43 PM
  • Anyone can just run a Google search.

    Dave SQL Developer

    Friday, September 6, 2013 6:43 PM
  • declare @x as time = '09:28:00'
    declare @y as time = '09:38:00'
    declare @z as time 
    set @z = DATEADD(s, datediff(s, @x, @y), '00:00:00')
    print @z


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, September 6, 2013 6:44 PM
  • * Sigh...

    Ok, this is not hardcoded. Here is what I am trying to do but it's not working out...

    datediff(hh, startdate, enddate)+':'+datediff(mm, startdate, enddate)+':'datediff(ss, startdate, enddate)


    Dave SQL Developer

    Friday, September 6, 2013 7:07 PM
  • Hi,

    You should adapt your formula as follows :

    The hours part is DATEDIFF(s, Start, End) \ 3600

    The minutes part is (DATEDIFF(s, Start, End) \ 60) % 60

    The seconds part is DATEDIFF(s, Start, End) % 60

    Convert them all to strings and concatenate them


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, September 6, 2013 7:16 PM
  • Incorrect syntax near '\ 3600'.

    Could you please give real syntax?


    Dave SQL Developer

    Friday, September 6, 2013 7:19 PM
  • Oops... typing mistake

    Here it is

    The hours part is DATEDIFF(s, Start, End) / 3600

    The minutes part is (DATEDIFF(s, Start, End) / 60) % 60

    The seconds part is DATEDIFF(s, Start, End) % 60


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, September 6, 2013 7:20 PM
  • OK, could you please write it out so it works. I have been working on this for several hours and would appreciate it. Copy and pasting from Google is not why we use this forum.

    Thank you kindly


    Dave SQL Developer

    Friday, September 6, 2013 7:26 PM
  • Sorry man this doesn't work at all.

    Could someone else help please?


    Dave SQL Developer

    Friday, September 6, 2013 7:28 PM
  • My result is zero. Again, please do not copy and paste from google to get the question answered.

    Dave SQL Developer

    Friday, September 6, 2013 7:29 PM
  • SELECT CAST(DATEDIFF(s, Start, End) / 3600 AS VARCHAR(2)) + ':' + CAST((DATEDIFF(s, Start, End) / 60) % 60 AS VARCHAR(2)) + ':' + CAST(DATEDIFF(s, Start, End) % 60 AS VARCHAR(2))


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Marked as answer by DaveDB Friday, September 6, 2013 7:32 PM
    Friday, September 6, 2013 7:29 PM
  •   Declare @StartDate as time = '09:28:00'
      Declare @EndDate as time =  '09:38:00'
      
      SELECT @StartDate, @EndDate , 
      Coalesce( Convert(varchar(5),DateDiff(day, (Cast(@EndDate as datetime)- Cast(@StartDate as datetime)),'1900-01-01'))  + ':' + Convert(varchar(10),(Cast(@EndDate as datetime)-Cast(@StartDate as datetime)), 108),'00:00:00:00') as [days:hh:mm:ss] 
     

    Friday, September 6, 2013 8:39 PM