<

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

Published on
12,031 Points
6,031 Views
Last Modified:
Approved
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))

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)

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')

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
0
Comment
Author:alainbryden
  • 2
  • 2
4 Comments
LVL 51

Expert Comment

by:Ted Bouskill
Personally I'm a big fan of creating opportunities to scale by offloading CPU activity from the central server to layers on other tiers.  We've recorded substantial performance improvements by doing so.

So all parsing is moved out to the application or presentation layers that are hosted on multiple cheap servers.  Because MS SQL doesn't support true clustered load balancing there is only one instance of SQL that is handling load while two or more servers are running the application and presentation layers and are rarely CPU bound.
0
LVL 47

Expert Comment

by:aikimark
@tedbilly

Did you mean to post that comment in this article?  It doesn't seem to relate to time formatting.
0
LVL 51

Expert Comment

by:Ted Bouskill
aikimarkl: Yes I did intend to comment on this article, not as an administrator.  I'm a big proponent of the right tool for the job.  Just because SQL can format dates doesn't mean it should.  I've never seen a pure SQL application.  The data always passes through layers written in other languages like .NET or Java which have very rich powerful high performance formatting functions.  So why not let SQL focus on what it does best which is execute queries and move this work out to the business or presentation layers?  Even reporting tools like SQL Reporting Services or Cognos have excellent date/time formatting functions.

If we are trying to educate with articles I'm proposing another point of view.  Format your dates and times in the non-SQL application layers.
0
LVL 47

Expert Comment

by:aikimark
@Tedbilly

Ah.  I understand.  Data formatting can be done in a presentation layer or application layer, rather than at the data layer.
http://msdn.microsoft.com/en-us/library/aa480039.aspx
http://en.wikipedia.org/wiki/Presentation_Layer

Back in the 90s, I advised formatting at the database server layer because servers were so much more powerful than desktop PCS.  I certainly couldn't justify that with today's chip speeds.  Today, system designers have a more difficult process with thin clients, thick clients, smart clients, smart phones, etc.
0

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month