Making date and time sense from the SQL Server Agent job history table
November 26, 2009 – 9:17 pmSometimes you’ve got to wonder why database developers do things the way the do. Take for example the sysjobhistory table in the SQL Server msdb database. Its got two awful columns namely run_date and run_time. Why are they awful you ask? Well because they are integer representations of the date and time. Just look at the following examples to see the stupidity.
| run_date | run_time | Actual |
| 20091123 | 230000 | 2009-11-23 23:00:00 |
| 20091123 | 230001 | 2009-11-23 23:00:01 |
| 20091123 | 234500 | 2009-11-23 23:45:00 |
| 20091124 | 0 | 2009-11-24 00:00:00 |
| 20091124 | 0 | 2009-11-24 00:00:01 |
| 20091124 | 1000 | 2009-11-24 00:10:00 |
| 20091124 | 3000 | 2009-11-24 00:30:00 |
| 20091124 | 10000 | 2009-11-24 01:00:00 |
| 20091124 | 10636 | 2009-11-24 01:06:36 |
Now if you’re wanting to do something meaningful with them you’ll want to represent them as a DATETIME data type most likely. After searching for a quick and easy way I decided to hack a quick bit of TSQL up and put it up here so hopefully others might benefit.
SELECT CONVERT(DATETIME,
STUFF(STUFF(CONVERT(VARCHAR, [run_date]), 7, 0, '-'), 5, 0, '-') +
' ' +
STUFF(STUFF(RIGHT('00000' + CONVERT(VARCHAR, [run_time]), 6), 5, 0, ':'), 3, 0, ':'), 120)
FROM [msdb]..[sysjobhistory]
Let me know in the comments if you found it useful or if you have a more elegant way. If you use it a lot, make a user-defined function taking in the two values. I leave that in your capable hands.
3 Responses to “Making date and time sense from the SQL Server Agent job history table”
What were they thinking? The mind boggles. This is even worse than storing the date and time as strings, as it’s one conversion further from the truth.
By Brady Kelly on Nov 26, 2009
Well I suppose its a legacy design issue from the days of Sybase or something like that. Perhaps they never had a DATETIME data type then the next best would be two 32-bit integers. I would have thought though that they would have put a new table down and created a view for backwards compatibility or something. They seem to have done it for other tables.
By Craig Nicholson on Nov 26, 2009
It’s been like this for many versions, but that’s no excuse. There are far better ways to express times, even using a string, than it’s using. The duration is stored in the same way. Have fun working out how long a job ran for…
Frankly, MSDB is a wonderful example of a terribly designed database. Don’t even get me started on sysjobschedules.
I had three UDFs that I used to use to turn the data into something actually usable.
By Gail on Nov 27, 2009