This morning I was greeted with a "jobs failed this morning because a database was down. Can you figure out which and rerun them? " task. I've done this (sort of) in WebFOCUS with the aid of Susannah's wonderful article but this felt like a SQL Server query to me.

I finally sat down and spent some time making this work in native SQL and have just sent off a script to my DBA to define 4 new views that are re-calcs of the originals with the new datetime fields added. Tomorrow, I'll build new masters to them and toss out the old ones.

Notes:
* This adds new fields, the old are still available.
* This is a native SQL Server solution and is meant to be defined in a view. If you are looking for a purely WebFOCUS soultion, go no further than Susannah's wonderful article.
* This is UTC independent. As you flip from DST and back again, this should pick up on that and automatically adjust accordingly (according to server locale). Note to my friends on the other side of the UTC divide, this assumes you are negative of the UTC divide. If you are positive of it, change these lines (removing the * -1): select datepart(hh,(getutcdate() - getdate())) * -1)
* This is timezone independent and can be run without any configuration (assuming your server is in the same locale as you are).

BOTLOG

SELECT  
STAT_BT_NAME
,JOB_DESC
,SCHED_ID
,CASTER_USER
,TELLME
,ERROR
,START_STAMP
,END_STAMP
, DATEADD(hh, (select datepart(hh,(getutcdate() - getdate())) * -1), DATEADD(ss, convert(bigint,SUBSTRING(start_stamp, PATINDEX('%[^0]%', start_stamp), LEN(start_stamp))) / 1000, '1970-01-01')) AS 'start_stamp_datetime'
, DATEADD(hh, (select datepart(hh,(getutcdate() - getdate())) * -1), DATEADD(ss, convert(bigint,SUBSTRING(end_stamp, PATINDEX('%[^0]%', end_stamp), LEN(end_stamp))) / 1000, '1970-01-01')) AS 'end_stamp_datetime'

from botlog

BOTSCHED

SELECT
SCHEDULEID
, PACKETID
, CASTER_USER
, JOBDESC
, ACTIVE
, PRIORITY
, RECOVERY
, RUNEVERY
, INTERVALTYPE
, WEEKDAYS
, DATES
, STARTDATE
, STARTTIME
, ENDDATE
, ENDTIME
, UPDATEDATE
, UPDATETIME
, NEXTRUNTIME
, DATEADD(hh, (select datepart(hh,(getutcdate() - getdate())) * -1), DATEADD(ss, convert(bigint,SUBSTRING(NEXTRUNTIME, PATINDEX('%[^0]%', NEXTRUNTIME), LEN(NEXTRUNTIME))) / 1000, '1970-01-01')) AS 'NEXTRUNTIME_datetime'
, LISTTYPE
, DISTLIST
, METHOD_CODE
, MAILHOST
, MAILUSER
, MAILFROM
, MAILSUBJECT
, MAILCOMPANY
, MULPART
, MULPARTTID
, ATTACHMENT
, FTPHOST
, FTPLOCATION
, FTPUSER
, FTPPASS
, FTPPUBLISH
, FTPPUBLISHNAME
, MR_OUTPUT_FOLDER
, SYNC
, NOTIFY_FLAG
, NOTIFY_ADDRESS
, NOTIFY_BRIEF
, NOTIFY_REPLY
, NOTIFY_SUBJECT
, ALERTJOB
, ALERTTID
, RESETEVERY
, RESETALERT
, TRIGGERED
, LIBEXPTYPE
, LIBEXPINT
, ADDRSERVERNAME
, INDEXFILE
, DELETEJOB
, AUDITOR
, LIBEMAIL
, LIBRARYURL
, ZIPRESULT
, ZIPFILENAME
, ADDREXECID
, ADDRSERVERTYPE
, LIBVALUEONLY
, REPEATSCHEDULE
, REPEATEVERYMINUTES
, REPEATEVERYHOURS
, REPEATUNTILTIME
, REPEATLASTMINUTES
, REPEATNEXTRUNTIME
, SFTP

FROM BOTSCHED

Hope this helps someone out.

- ABT

This message has been edited. Last edited by: ABT,

Cool! Thx.