More Dating Advice

There must be some sort of synchronicity at work - just this morning I was blogging on how to convert different string and integer date representations into "real" datetime values, and then this afternoon I found myself needing to include a "seconds formatted as time" column in a view I was building on top of an audit dimension table. This is something I've done many times before, so I figured I could just Google it and get some sample code from any one of a million places online.

But this was not the case. I couldn't find any samples at all. So I dug through my old project code (and dug, and dug, and dug) until I found what I was looking for. And then I had to update it to include millisecond support, because the old code I found didn't go to this grain. Anyway, here's the deal:

1) Start with a table that has a start time and an end time column, like so:

CREATE TABLE DateFormatTemp
(
[ExecStartDT] DATETIME NOT NULL,
[ExecStopDT] DATETIME NOT NULL
)

This looks pretty familiar, right? And it's very simple to DATEDIFF these two fields to find out how many seconds (or whatever) there were between the two dates. But your users (and that probably includes you, too) don't want to have to translate a second count into hours, minutes and seconds. I know I don't, so...

2) Use the T-SQL DATEDIFF, CASE and string concatenation functions to format the results into something more useful [1]:

  SELECT [ExecStartDT]
        ,[ExecStopDT]
        ,DATEDIFF (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))    AS ExecutionTimeInSeconds
        ,CASE -- Hours
            WHEN (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) >= 10 THEN
               CAST (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) AS CHAR (1)) AS CHAR(2))
         END + ':' +
         CASE -- Minutes
            WHEN (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) % 60 >= 10 THEN
               CAST (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (1)) AS CHAR(2))
         END + ':' +
         CASE  -- Seconds
            WHEN (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) % 60 >= 10 THEN
               CAST (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (1)) AS CHAR(2))
         END +
         CASE -- Milliseconds
            WHEN DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 < 10 THEN
               '.00' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
            WHEN DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 < 100 THEN
               '.0' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
            ELSE
               '.' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
         END                                                               AS ExecutionTimeFormatted
    FROM [DateFormatTemp]
ORDER BY ExecutionTimeInSeconds ASC;

When you execute this you get back something that looks vaguely[2] like this:

ExecStartDT      ExecStopDT              Exec... ...Formatted

---------------- ----------------------- ------- ------------

2007-05-28 17:00 2007-05-28 17:03:13.817 193     00:03:13.816

2007-05-28 17:00 2007-05-28 17:03:31.323 211     00:03:31.323

2007-05-28 17:00 2007-05-28 17:05:24.547 324     00:05:24.546

2007-05-28 16:30 2007-05-28 17:04:10.920 2050    01:34:10.920

2007-05-28 16:30 2007-05-28 17:05:19.193 2119    01:35:19.193

2007-05-28 16:20 2007-05-28 17:04:05.973 2645    01:44:05.973

2007-05-28 16:10 2007-05-28 17:03:59.680 3239    01:53:59.680

2007-05-28 16:00 2007-05-28 17:03:42.323 3822    01:03:42.323

2007-05-28 15:00 2007-05-28 17:03:46.710 7426    02:03:46.710

(9 row(s) affected)

This is much more useful than just having the difference in seconds.

Of course, you can put this code in a T-SQL UDF, or have MUCH simpler code that does the same thing in a SQL CLR UDF, but in my case I only need this logic once in each database, and do not want the added complexity of managing SQL CLR anything, so this does exactly what I need.

[1] Useful like the formatting here is not. This code is just too wide to fit nicely here.

[2] I say "vaguely" because I've edited the column headings and start times to make the values fit better on the blog.