SQL - Formatting a time (or DateDiff) as hours + mins + secs, etc...

SQL Server does not have a very nice way to convert dates and times to formatted strings. There are preset formats using the CONVERT function, but you would need to look up the integer corresponding to the format you want, which may or may not be there. http://msdn.microsoft.com/en-us/library/ms187928.aspx

Point in Time vs. Time Span
Note that there is a difference between a DATE or TIME, and a TIMESPAN (which is usually expressed as an INTEGER). A Date, Time, or DateTime (conceptually) is a fixed moment, and is the result you get from a function such as GETDATE(). You can do math with a date and another date, but you cannot treat a date as a number. A time(span) or elapsed time is really just like a number, with units of measurement usually in a format such as seconds or milliseconds. This might be the amount of time that has passed between now and Epoch (usually 1900 for computing), since the beginning of the day, the difference between any two dates, or the time it took a procedure to run.

Before you decide how you're going to format your time, you need to use the above explanations to figure out whether you're dealing with a point in time (a DATE) or a timespan (an INTEGER).


When your time is a DATE (point in time)
This is useful when you are dealing with DATE types (such as the result of a Date() function call) It also works with TIME, DATETIME, and similar data types.

If you are working with a Date data type, then all the formatting you need can be more or less generated using the DATEPART() function. Simply call Datepart( [year/quarter/month/day/week/weekday/hour/minute/second/millisecond], @dateVal ), and it will spit out an integer corresponding to that value. The full use of this function is fairly well specified at MSDN.

Here is a sample use:

Declare @start_time DATETIME
                      Set @start_time = GETDATE()
                      
                      PRINT ('Current time is: ' + DATEPART(hour,@start_time) + ':' +
                      			     DATEPART(minute,@start_time) + ':' +
                      			     DATEPART(second,@start_time) + '.' +
                      			     DATEPART(millisecond,@start_time))
                      

Select all Open in new window

of if you want exactly that format, just use the hard coded integer convert code '114':

CONVERT(CHAR(12), GETDATE(), 114)
                      

Select all Open in new window

In this case, Microsoft has decided that 114 corresponds to the format "hh:mm:ss.sss". I try not to get into the habit of using that style of date/time formatting though, because there are a limited number of variations, and if you want to customize them you have to start hacking around with the returned character string using Right(), Left() and Mid().


When your time is an INTEGER (time span)
This is useful if you are dealing with elapsed times, such as the result of the DATEDIFF() function call (which returns an integer).

My solution was just to algebraically break down the time (in milliseconds) into seconds, minutes, hours, etc, and then display each component however I choose. It's not as simple as a one-line solution using "CAST(@time AS nvarchar(max))", but it's much more customizable.

The following example code will take the difference between some starting time, and the current time, and break it into hours through milliseconds:

Declare @start_time DATETIME
                      Set @start_time = GETDATE()
                      .....
                      DECLARE @tt int, @tthrs int, @ttmins int, @ttsecs int, @ttmsecs int
                      Set @tt = DATEDIFF(millisecond, @startDatetime, @endDatetime)
                      Set @tthrs =  floor(  @tt / (1000*60*60) )
                      Set @ttmins = floor( (@tt / (1000*60)) - (60*@tthrs) )
                      Set @ttsecs = floor( (@tt / (1000)) - (60*(@ttmins+60*@tthrs)) )
                      Set @ttmsecs = floor( @tt - (1000*(@ttsecs+60*(@ttmins+60*@tthrs))) )
                      PRINT ('Total Time Taken: ' + CONVERT(nvarchar(max),@tthrs) + ' hours, ' +
                      			      CONVERT(nvarchar(max),@ttmins) + ' minutes, ' +
                      			      CONVERT(nvarchar(max),@ttsecs) + ' seconds, ' +
                      			      CONVERT(nvarchar(max),@ttmsecs) + ' milliseconds')
                      

Select all Open in new window

You can see this is a fairly simple pattern. You can add days and years by adding 24, and 365.25 to the divisor. Unfortunately, this method does not let you get results in months or quarters since there is no straightforward mathematical relationship between those units and the basic units of measurement of time.

I really just use this code when providing output for the amount of time taken to run a lengthy stored procedure at the end of a log of messages, but it can be used in any case where you have a value in milliseconds, and you wish to break it up into more human-readable chunks of information.

--
Alain Bryden