Author Archives: John Perry

What has run, during the last month, on Sunday’s between 1:59 AM and 3?
Use the to check on daylight saving’s jobs which might be missed.

SELECT MAX(master_admin.dbo.fn_INTtoDateTime(run_date, run_time) ),
j.name
FROM msdb.dbo.sysjobhistory h WITH (NOLOCK)
JOIN msdb.dbo.sysjobs j WITH (NOLOCK)
ON j.job_id = h.job_id
WHERE datename(dw, convert(varchar(10),run_date, 112) ) = 'Sunday'
AND run_time BETWEEN 015900 AND 030000
AND step_id = 0
AND run_date >= CONVERT(VARCHAR(8), DATEADD(day, -31, GETDATE()), 112)
GROUP BY j.name ,DATENAME(weekday, CONVERT(VARCHAR(10), run_date, 112))
ORDER BY
name

This uses one of my functions. fn_INTtoDateTime

I use this when querying msdb.dbo.sysjobhistory.

USE [master_admin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

GO

This code shows the Symbol and HEX as well as DEC. Still to go is OCT.

WITH Symbols
 AS (
 SELECT -1 code,
 CHAR(ASCII(' ')) Symbol
 UNION ALL
 SELECT code + 1,
 CHAR(code + 1)
 FROM Symbols
 WHERE code + 1 <= 255
 )
 SELECT Symbol,
 ASCII(Symbol) AS [DEC],
 master.dbo.fn_varbintohexstr(CAST(Symbol AS VARBINARY)) AS HEX
 --,(ASCII(Symbol) % 8) AS [OCT]
 FROM Symbols
 ORDER BY [DEC]
OPTION (MAXRECURSION 256)