As a DBA, I find myself spelunking through data and often having to present it in a somewhat human-friendly format. This is where my skills with Crystal Reports come in quite handy. I joke that I’m a Crystal Reports Jedi Master. Truth be told, I’ve just been working with the product entirely way too long.

One of my biggest annoyances with SQL Server is how it stores dates and times in some of the MSDB tables. In this case, I’m looking at sysjobhistory. SQL Server stores these as integers. Worse yet, the date and time are in different columns. Crystal Reports has no idea that these are actually date and time values.   You get something that looks like this.

Screenshot: Date and Time in different columns

It’s hard to do calculations on this, let alone have it look presentable. To fix that problem, I give you the Crystal Reports formula from hell.

datetime(
tonumber(left(cstr({sysjobhistory.run_date},0,””),4)),
tonumber(left(right(cstr({sysjobhistory.run_date},0,””),4),2)),
tonumber(right(cstr({sysjobhistory.run_date},0,””),2)),

IF len(cstr({sysjobhistory.run_time},0,””)) =6
THEN tonumber(left(cstr({sysjobhistory.run_time},0,””),2))
ELSE IF len(cstr({sysjobhistory.run_time},0,””)) =5
THEN tonumber(left(cstr({sysjobhistory.run_time},0,””),1))
ELSE 0,
IF (len (cstr({sysjobhistory.run_time}))) < 3 THEN 0 ELSE tonumber(left(right(cstr({sysjobhistory.run_time},0,””),4),2)),

tonumber(right(cstr({sysjobhistory.run_time},0,””),2))
)

Is it ugly? Yes.
Does it look hacked together? Yes.
Is it messy? Absolutely.
Is there a better way? Probably.
Does this work? Hell yeah!

So now we have something that looks much more presentable in a report.

Screenshot: After the formula

I like this much better. By putting the date and time into a formula that is in a datetime format, it allow us to do things like sort, group, and calculate.

To be honest, as great as this works, I want to find a better way, without all of those type conversions. If I find it, I’ll let you know.