You need to figure out how to parse GETDATE () in order to compare it with the HHMMSS value in the table to find elapsed time

Question

I need to find a way to create an alert that will warn me about the elapsed time of a batch job other than SQL. Information about the batch job is written to the database.

I have a table called F986110 that records the time it took to send these batch jobs. The date is recorded in the JCSBMDATE field as a six-digit value 1YYDDD, where YY = the last two digits of the current year, and DDD is the current day, numbered from January 1. For example, February 25, 2011 will be 111056. The time is recorded in the JCSBMTIME column in military-style hhmmss format, but does not lead 0 at a time until 10 a.m. There is also a status column that indicates processing (P), waiting (W), and execution (D).

My task is to send an alert for all jobs that are in P or W state for more than 15 minutes. For a while I hit my head about it. I found a lot of good material that points me in the right direction, but I need to parse the system time, compare it with each returned record and notify those (if any) that match my skills.

Answer 1

Convert JCSBMDATE (1YYDDD) and JCSBMTIME to datetime

declare @t table (JCSBMDATE char(6),JCSBMTIME varchar(6))
insert @t select '111056', '104602'
insert @t select '199365', '81602'

select dateadd(d,
               right(JCSBMDATE,3)-1,
               convert(datetime,'01/01/' + substring(JCSBMDATE,2,2),3)) +
       convert(datetime, stuff(
                         stuff(JCSBMTIME,
                               len(JCSBMTIME)-3,0,':'),
                               len(JCSBMTIME),0,':'))
from @t

Index-friendly inverse transform

select '1' +
       right(year(getdate()),2) +
       right(1000+datepart(dy, getdate()),3) as JCSBMDATE,
       right(1+replace(convert(char(8),getdate(),8),':',''),6) as JCSBMTIME

You can easily configure getdate()with dateadd(mi,-15,getdate())to get the values ​​of JCSBMDATE and JCSBMTIME 15 minutes ago.