I searched on google for "sql server convert sysjobschedules to datetime" and the very first hit has the following function.

create FUNCTION [dbo].[udfGetDateTimeFromInteger]

(

@intDate int,

@intTime int

)

RETURNS datetime

AS BEGIN

-- Declare the return variable here

DECLARE @DT_datetime datetime = NULL,

@str_date varchar(11),

@str_time varchar(8)

if(@intDate is not null and @intDate > 0)

begin

select @str_date = CONVERT(varchar(11),@intDate)

select @str_date = SUBSTRING(@str_date,1,4)+'/'+SUBSTRING(@str_date,5,2)+'/'+SUBSTRING(@str_date,7,2)

if @intTime=0

select @str_time ='000000'

else

select @str_time = right('0'+CONVERT(varchar(11),@intTime),6)

select @str_time = SUBSTRING(@str_time,1,2)+':'+SUBSTRING(@str_time,3,2)+':'+SUBSTRING(@str_time,5,2)

select @DT_datetime = CAST(@str_date+' '+@str_time as datetime)

end

-- Return the result of the function

RETURN @DT_datetime

END

Then to use it to get your time only i did this.

SELECT next_run_date ,

next_run_time ,

convert(varchar, dbo.udfGetDateTimeFromInteger(next_run_date, next_run_time), 108)

FROM msdb.dbo.sysjobschedules AS s

You can either use this function of roll it into your select (which might be a bit tricky). Your method does also work and is probably bit quicker, although unless you have a ton of sql jobs performance really isn't going to be much of a factor here.

--edit-- fat fingers strike again. 😛