Sometimes 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.
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.
STUFF(STUFF(CONVERT(VARCHAR, [run_date]), 7, 0, '-'), 5, 0, '-') +
' ' +
STUFF(STUFF(RIGHT('00000' + CONVERT(VARCHAR, [run_time]), 6), 5, 0, ':'), 3, 0, ':'), 120)
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.