How to convert Time from int to Real-time

Hi all.

I have posted this question in another forum but no one has so far been able to provide a solution to the problem. Since I know Database Journal always has very informative and enlightening posts, I figured I'd post the question here in hopes that some guru can provide an answer.

If you're running SQL 2000 and have any jobs that have been executed, you could perform a query as such:

select last_run_time from msdb.dbo.sysjobsteps

and receive returned values that contain the last time a job was executed "stored in integer datatype" columns. See ->
sp_help sysjobsteps.

In SQL 2005 I believe the concept is the same. I think the intent of Microsoft had it mind for doing this was to store the date separate from the time values which won't work using the datetime datatype and I have read this in documentation in the past.

The challenge is to convert that data into a humanly legible 12 or 24 hour time format like 11:00 AM or 02:45:39.

Does anyone have any suggestions or clues to assist in resolving this problem??? :(

Thanks.

Hi Mindscape,

I use the datename function in some syntax like this for presenting datetime data types in human readible format. It works in SQL 2000 and 2005:

declare @today as datetime
set @today = getdate()
select datename(month,@today)+space(1)+
datename(day,@today)+','+space(1)+
datename(year,@today)

DateTime when expressed as a float uses the fractional part to describe the time and the whole number part to describe the date.
Looking at my sql 2005 box, the values for last_run_time in msdb.dbo.sysjobsteps appear to be this fractional time part but stored as an int. If I am correct that this is what the last_run_time is stored as, then you can convert to a DateTime of the correct time on 1900-01-01 by doing:

select
case last_run_time
when 0 then cast(0 as datetime)
else cast(cast(last_run_time as float) / power(10, floor(log10(last_run_time))+1) as datetime)
end as last_run_time_dt
from msdb.dbo.sysjobsteps

Notes:
-have to case out the 0 values or else it errors
-there may be some simpler math approach to reliably convert both 72 to .72 and 840 to .84, but I'm not sure what it is. So this method determines what power of 10 we need to divide last_run_time to turn it into a fraction between < 1 and >= .1

Hope that helps!
-Adrian Hains

I have an ugly, but hopefully correct way of converting those times:

REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, last_run_time))) + CONVERT(varchar, last_run_time)), 3, 0, ':'), 6, 0, ':'))

And if you need to combine the time with the last_run_date, it gets even uglier:

REPLACE(CONVERT(varchar, CONVERT(datetime, CONVERT(char(10), CONVERT(datetime, CONVERT(varchar, last_run_date)), 101) + ' ' + REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, last_run_time))) + CONVERT(varchar, last_run_time)), 3, 0, ':'), 6, 0, ':'))), 109), ':000', '')

Quote:

---

Originally Posted by nosepicker

I have an ugly, but hopefully correct way of converting those times:

---

Thank you for that, it makes my method look not hacky at all in comparison :)

Wow, I've written some pretty ugly T/SQL but nosepicker's surpasses all. -- although, it does appear to work. I wouldn't put anything like it into production.

This example takes a julian date, expressed as a decimal, and converts it to human readable hour with meridian:

select substring(cast(convert(datetime, 39750.33333335, 101) as varchar),13,20)