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:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, “Courier New”, Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

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.

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s