Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Format Length of time to 00:00:00

    I am trying to get the time from a DATEDIFF to read as '00:00:00' format. So if the length of time is 1 hour 20 mins it reads '01:20:00'

    I am again stuck and currently have this but dont know where to go from here?

    UPDATE MyTable
    SET LengthofTime = DATEDIFF(MINUTE, StartTime, EndTime)

    This give me for 1hr 20 mins 80 I need it to read '01:20:00' what is the best and easiest way to do this?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will work, though I'm not sure if it is the most efficient approach:
    Code:
    SELECT StartTime, 
    	EndTime, 
    	RIGHT('0' + CAST(DATEDIFF(minute, StartTime, EndTime) / 60 as VARCHAR(2)), 2) + ':' +
    	RIGHT('0' + CAST(DATEDIFF(minute, StartTime, EndTime) % 60 as VARCHAR(2)), 2) + ':00'
    from MyTable
    You do realise that once the number of minutes are cast into a string, manipulating (adding, subtracting, ...) the elapsed time only became harder.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    That is great - Thank you.

    I am aware of the difficulties of manipulating it as a string unfortunatly this then feeds through to another part of the system that does the calcs on that format. Probably worth changing at some point but to much involved for the time being.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •