Crystal Reports Formula for MSDB Dates and Times

3

September 10, 2009 by Mike Hillwig

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

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

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.

  • Sankar

    Mike,

    There are different ways to skin this and here is quick attempt. But a robust and cleaner solution will be using the numbers table/calendar table approach.

    create table #test( run_date int, run_time int)
    insert #test
    select 20090821, 70000 union
    select 20090821, 80000 union
    select 20090821, 121500

    select dateadd (ss,
    (right(right(‘0’ + cast (run_time as varchar(6)), 6),2)+
    substring(right(‘0’ + cast (run_time as varchar(6)), 6), 3, 2)*60+
    left(right(‘0’ + cast (run_time as varchar(6)), 6),2)*60*60)
    , convert(datetime, cast(run_date as varchar(8)), 101)) ScheduleDatetime
    from #test

  • Steve Munson

    There is a considerably easier way, and I’ve been using Crystal for at least the last 15 years. Try the following as a formula:

    StringVar DString := Trim(CStr(sysjobhistory.rundate));
    StringVar TString := Right(“00000” & Trim(CStr(sysjobhistory.run_time)),6);
    DateTimeVar DTM := DateTime(Left(DString,4),Mid(DString,5,2),Right(DString,2),Left(TString,2),Mid(TString,4,2),Right(TString,2));
    DTM;

    Take note of the additional 5 zeros added to the runtime once converted to string, and that I then take the right-most 6 characters. This guarantees correct alignment of the time values, and string manipulation does the rest as the date values are already aligned. It seems likely this would perform better because of no ifs, but without testing on a large dataset, it might be hard to determine for certain, and I don’t have Crystal available to me right now. I’d love to know, however, so feel free to test and publish the results.

    Regards,

    Steve

  • Phil

    Another way to skin this cat is to convert it to a datetime value in the query.

    SELECT jb.[name]
    , CASE WHEN [run_date] > 0
    THEN CONVERT (DATETIME, RTRIM([run_date]))
    + ( [run_time] * 9
    + [run_time] % 10000 * 6
    + [run_time] % 100 * 10
    ) / 216e4
    ELSE NULL
    END as [run_datetime]
    from [msdb].[dbo].[sysjobhistory] jsvr
    INNER JOIN [msdb].[dbo].[sysjobs] jb
    ON jsvr.[job_id] = jb.[job_id]