CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS
BEGIN
DECLARE @vcMsg VARCHAR(128),
@nH INT,
@nM INT,
@nS INT,
@vcH VARCHAR(48),
@vcM VARCHAR(48),
@vcS VARCHAR(48)
SELECT @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
@nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
@nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
@vcH = CASE WHEN @nH = 0 THEN ''
WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
ELSE CAST(@nH AS VARCHAR) + ' hours, '
END,
@vcM = CASE WHEN @nM = 0 THEN ''
WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
ELSE CAST(@nM AS VARCHAR) + ' minutes, '
END,
@vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
ELSE CAST(@nS AS VARCHAR) + ' seconds'
END,
@vcMsg = @vcH + @vcM + @vcS
RETURN @vcMsg
END
[master_admin].[dbo].[fn_INTtoDateTime]
CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
@vcDate VARCHAR(8),
@vcTime VARCHAR(6)
)
RETURNS DATETIME
AS
BEGIN
SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)
IF @vcDate = '00000000'
SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
'-', ''), ':', ''),
SPACE(1), ''), 8)
RETURN (
CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' +
SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2)
AS DATETIME)
)
END
SQL Agent: What ran last night after time …
------------------------------------------------------------------------------------------------------------------------
--
-- What ran last night after time ...
--
SELECT [master_admin].[dbo].[fn_INTtoDateTime]([run_date], [run_time]) AS [RunDate],
DATENAME(WEEKDAY, CONVERT(VARCHAR(10), [run_date], 112)),
[j].[name],
[master_admin].[dbo].[fn_RunDuration]([run_duration]),
[h].[step_id],
[s].[step_name],
[command] AS [StepCommand],
[instance_id]
FROM [msdb].[dbo].[sysjobhistory] [h] WITH (NOLOCK)
JOIN [msdb].[dbo].[sysjobs] [j] WITH (NOLOCK)
ON [j].[job_id] = [h].[job_id]
JOIN [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
ON [s].[job_id] = [j].[job_id]
AND [s].[step_id] = [h].[step_id]
WHERE [run_date] >= CONVERT(VARCHAR(8), DATEADD(DAY, -0, GETDATE()), 112)
AND [h].[run_time] >= 021439
ORDER BY [RunDate]
Required objects
[fn_INTtoDateTime]
[fn_RunDuration]