Is there a canned BO report to show BCA stats/errors?
I have to go to a morning meeting each day where we look at metrics and such…I would like to be able to show them how many BCA jobs fail and why.
LotusSutol (BOB member since 2003-06-30)
You can get this info from repo tables DS_PENDING_JOB and obj_m_documnts
reemagupta (BOB member since 2002-09-18)
I have a report that does exactly what you’re looking for. I section it by each user and then by the status of each scheduled job.
When I get into work tomorrow I’ll post a followup with the SQL. If you give me your email, I can send you a copy of the report as well.
tscoccol 
(BOB member since 2003-11-15)
Here’s the code I use.
select a.m_actor_c_name
,CASE
WHEN FREQUENCY IN (4,6) then
'Once'
WHEN FREQUENCY in (64,66) then
'MONTHLY on ' ||
rtrim(
CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN '1,' ELSE null END ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END ,power(2,30)),power(2,30),'2,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,29)),power(2,29),'3,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,28)),power(2,28),'4,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,27)),power(2,27),'5,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,26)),power(2,26),'6,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,25)),power(2,25),'7,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,24)),power(2,24),'8,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,23)),power(2,23),'9,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,22)),power(2,22),'10,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,21)),power(2,21),'11,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,20)),power(2,20),'12,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,19)),power(2,19),'13,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,18)),power(2,18),'14,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,17)),power(2,17),'15,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,16)),power(2,16),'16,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,15)),power(2,15),'17,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,14)),power(2,14),'18,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,13)),power(2,13),'19,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,12)),power(2,12),'20,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,11)),power(2,11),'21,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,10)),power(2,10),'22,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,9)),power(2,9),'23,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,8)),power(2,8),'24,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,7)),power(2,7),'25,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,6)),power(2,6),'26,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,5)),power(2,5),'27,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,4)),power(2,4),'28,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,3)),power(2,3),'29,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,2)),power(2,2),'30,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,1)),power(2,1),'31,',null) ||
decode(bitand(CASE WHEN SIGN(2147483648 - DAY_TIMING) < 1 THEN DAY_TIMING -2147483648 ELSE DAY_TIMING END,power(2,0)),power(2,0),'LAST,',null) ,',')
WHEN FREQUENCY in (8,10) then 'HOURLY' ||
(CASE WHEN DAY_TIMING <24 then ' at ' || DAY_TIMING
WHEN instr(DAY_TIMING,'0',1,3) > 0 then
' between ' || substr(DAY_TIMING,1,instr(DAY_TIMING,'0',1,2)-1) || ' and ' || substr(DAY_TIMING,instr(DAY_TIMING,'0',1,2)+1)
WHEN instr(DAY_TIMING,'0',1,2) = length(DAY_TIMING) then
' between ' || substr(DAY_TIMING,1,instr(DAY_TIMING,'0',1,1)-1) || ' and ' || substr(DAY_TIMING,instr(DAY_TIMING,'0',1,1)+1)
WHEN instr(DAY_TIMING,'0',1,2) > 0 then
' between ' || substr(DAY_TIMING,1,instr(DAY_TIMING,'0',1,2)-1) || ' and ' || substr(DAY_TIMING,instr(DAY_TIMING,'0',1,2)+1)
ELSE
' between ' || substr(DAY_TIMING,1,instr(DAY_TIMING,'0',1)-1) || ' and ' || substr(DAY_TIMING,instr(DAY_TIMING,'0',1)+1)
END)
WHEN FREQUENCY in (16,18) then 'DAILY every ' ||
( decode(bitand(DAY_TIMING,power(2,21)),power(2,21),1,0) +
decode(bitand(DAY_TIMING,power(2,22)),power(2,22),2,0) +
decode(bitand(DAY_TIMING,power(2,23)),power(2,23),4,0) +
decode(bitand(DAY_TIMING,power(2,24)),power(2,24),8,0) +
decode(bitand(DAY_TIMING,power(2,25)),power(2,25),16,0) +
decode(bitand(DAY_TIMING,power(2,26)),power(2,26),32,0) +
decode(bitand(DAY_TIMING,power(2,27)),power(2,27),64,0)) || ' week(s) on ' ||
rtrim(
decode(bitand(DAY_TIMING,power(2,3)),power(2,3),'MON,',null) ||
decode(bitand(DAY_TIMING,power(2,4)),power(2,4),'TUE,',null) ||
decode(bitand(DAY_TIMING,power(2,5)),power(2,5),'WED,',null) ||
decode(bitand(DAY_TIMING,power(2,6)),power(2,6),'THU,',null) ||
decode(bitand(DAY_TIMING,power(2,7)),power(2,7),'FRI,',null) ||
decode(bitand(DAY_TIMING,power(2,8)),power(2,8),'SAT,',null) ||
decode(bitand(DAY_TIMING,power(2,9)),power(2,9),'SUN,',null)
,',')
WHEN FREQUENCY in (32,34) then 'WEEKLY every ' ||
( decode(bitand(DAY_TIMING,power(2,21)),power(2,21),1,0) +
decode(bitand(DAY_TIMING,power(2,22)),power(2,22),2,0) +
decode(bitand(DAY_TIMING,power(2,23)),power(2,23),4,0) +
decode(bitand(DAY_TIMING,power(2,24)),power(2,24),8,0) +
decode(bitand(DAY_TIMING,power(2,25)),power(2,25),16,0) +
decode(bitand(DAY_TIMING,power(2,26)),power(2,26),32,0) +
decode(bitand(DAY_TIMING,power(2,27)),power(2,27),64,0)) || ' week(s) on ' ||
rtrim(
decode(bitand(DAY_TIMING,power(2,3)),power(2,3),'MON,',null) ||
decode(bitand(DAY_TIMING,power(2,4)),power(2,4),'TUE,',null) ||
decode(bitand(DAY_TIMING,power(2,5)),power(2,5),'WED,',null) ||
decode(bitand(DAY_TIMING,power(2,6)),power(2,6),'THU,',null) ||
decode(bitand(DAY_TIMING,power(2,7)),power(2,7),'FRI,',null) ||
decode(bitand(DAY_TIMING,power(2,8)),power(2,8),'SAT,',null) ||
decode(bitand(DAY_TIMING,power(2,9)),power(2,9),'SUN,',null)
,',')
WHEN FREQUENCY in (128,130) then 'MONTHLY INTERVAL every ' ||
( decode(bitand(DAY_TIMING,power(2,21)),power(2,21),1,0) +
decode(bitand(DAY_TIMING,power(2,22)),power(2,22),2,0) +
decode(bitand(DAY_TIMING,power(2,23)),power(2,23),4,0) +
decode(bitand(DAY_TIMING,power(2,24)),power(2,24),8,0) +
decode(bitand(DAY_TIMING,power(2,25)),power(2,25),16,0) +
decode(bitand(DAY_TIMING,power(2,26)),power(2,26),32,0) +
decode(bitand(DAY_TIMING,power(2,27)),power(2,27),64,0)) || ' ' ||
rtrim(
decode(bitand(DAY_TIMING,power(2,13)),power(2,13),'1st,',null) ||
decode(bitand(DAY_TIMING,power(2,14)),power(2,14),'2nd,',null) ||
decode(bitand(DAY_TIMING,power(2,15)),power(2,15),'3rd,',null) ||
decode(bitand(DAY_TIMING,power(2,16)),power(2,16),'4th,',null) ||
decode(bitand(DAY_TIMING,power(2,17)),power(2,17),'Last,',null)
,',') || ' ' ||
rtrim(
decode(bitand(DAY_TIMING,power(2,3)),power(2,3),'MON,',null) ||
decode(bitand(DAY_TIMING,power(2,4)),power(2,4),'TUE,',null) ||
decode(bitand(DAY_TIMING,power(2,5)),power(2,5),'WED,',null) ||
decode(bitand(DAY_TIMING,power(2,6)),power(2,6),'THU,',null) ||
decode(bitand(DAY_TIMING,power(2,7)),power(2,7),'FRI,',null) ||
decode(bitand(DAY_TIMING,power(2,8)),power(2,8),'SAT,',null) ||
decode(bitand(DAY_TIMING,power(2,9)),power(2,9),'SUN,',null) ||
decode(bitand(DAY_TIMING,power(2,10)),power(2,10),'WEEKDAY,',null) ||
decode(bitand(DAY_TIMING,power(2,11)),power(2,11),'DAY,',null) ||
decode(bitand(DAY_TIMING,power(2,12)),power(2,12),'BUSINESS DAY,',null)
,',')
END as Frequency
,case
when t.job_status = 0 then 'Success'
when t.job_status = 1 then 'Failure'
when t.job_status = 2 then 'Waiting'
when t.job_status = 3 then 'Running'
when t.job_status = 4 then 'Suspended'
end as stat
,case when t.job_status in (2,3) then null
else to_date('1970-12-15','YYYY-MM-DD') + (t.begin_time / (24 * 60 * 60)) - ( 5 /24)
end as BCA_Begin_Time
,to_date('1970-12-15','YYYY-MM-DD') + (t.begin_date / (24 * 60 * 60)) as Scheduled_Begin_Date
,case when t.job_status in (2,3) then null
else to_date('1970-12-15','YYYY-MM-DD') + (t.start_datetime / (24 * 60 * 60)) - ( 5 / 24)
end as Server_Start_Datetime
,case when t.job_status in (2,3) then null
else to_date('1970-12-15','YYYY-MM-DD') + (t.end_datetime / (24 * 60 * 60)) - (5 / 24)
end as Server_End_Datetime
,case when t.job_status <> 2 -- Success
then ((to_date('1970-12-15','YYYY-MM-DD') + (t.end_datetime / (24 * 60 * 60)) - (5 / 24))
- (to_date('1970-12-15','YYYY-MM-DD') + (t.start_datetime / (24 * 60 * 60)) - ( 5 / 24) ) )
* 24 * 60
else null
end as Minutes_Run
,to_date('1970-12-15','YYYY-MM-DD') + (t.submit_datetime / (24 * 60 * 60)) - (5 / 24) as Submit_Date
,to_date('1970-12-15','YYYY-MM-DD') + (t.expiration_date / (24 * 60 * 60)) - (5/ 24) as Exper_Date
,rtrim(
Case When instr(t.job_script,'1,',1) > 0 then 'Refreshing' Else Null End ||
Case When instr(t.job_script,'2,',1) > 0 then ' ,Printing' Else Null End ||
Case When instr(t.job_script,'3,',1) > 0 then ' ,Web Server' Else Null End ||
Case When instr(t.job_script,',4',1) > 0 then ' ,Custom Macros' Else Null End ||
Case When instr(t.job_script,',6',1) > 0 then ' ,Channel' Else Null End ||
Case When instr(t.job_script,',7',1) > 0 then ' ,Save as RTF' Else Null End ||
Case When instr(t.job_script,',8',1) > 0 then ' ,Save as Text' Else Null End ||
Case When instr(t.job_script,',9',1) > 0 then ' ,Save as PDF' Else Null End
) As Actions
,a2.m_actor_c_name as Distributed_User
,t.job_script
,t.job_desc
,t.error_text
from bo_repos.ds_pending_job t
,bo_repos.ds_user_list dl
,bo_repos.obj_m_actor a
,bo_repos.obj_m_actor a2
,bo_repos.obj_m_documents d
where t.user_submit_id = a.m_actor_n_id
and t.document_id = d.m_doc_n_id
and t.document_id = dl.document_id (+)
and dl.user_id = a2.m_actor_n_id (+)By the way, my setup is:
BO Full-client: 5.1.6
Repository Database: Oracle 8.1.7
TimeZone: Eastern Standard
The reason I mention this is because each of these impacts the SQL.
Oracle 8.1.7 has a bug with the BITAND function, which is why you see all the funky code up top where we parse the monthly scheduled info.
Also, when you see a date calculation like:
t.start_datetime / (24 * 60 * 60)) - ( 5 / 24)
The (5 / 24) part subtracts 5 hours, which is the difference in time between Eastern Standard and GMT, which is what BO uses when it stores date-time info in the repository.
If you are in a different timezone, adjust that calcuation accordingly.
Let me know if you have any questions.
tscoccol (BOB member since 2003-11-15)
thanks! i will see if this will work for me…
fyi my email is mpait@aeraenergy.com
LotusSutol (BOB member since 2003-06-30)
here’s what we use. the code is not as involved and I’m not sure it will give you everything you want but we use this report to monitor the BCA and break it out by Running, Successful, Waiting, All, Failed, and Failed with Error Message within the BO report, which we have built off a Universe called BC Audit. I can send it to you if you are interested. Hopefully, the SQL will help you get started. You may have to play with some of the funky date stuff. - good luck
SELECT
SUBMITING_USER.M_ACTOR_C_NAME,
OBJ_M_DOCUMENTS.M_DOC_C_NAME,
to_char((to_date('14-12-1970 11:00:00', 'DD-MM-YYYY HH24:MI:SS') + DS_PENDING_JOB.START_DATETIME/(24*60*60)+(7/24)), 'mm/dd/yy HH24:mi:ss'),
to_char((to_date('14-12-1970 11:00:00', 'DD-MM-YYYY HH24:MI:SS') + DS_PENDING_JOB.END_DATETIME/(24*60*60)+(7/24)), 'mm/dd/yy hh24:mi:ss'),
to_char((to_date('14-12-1970 11:00:00', 'DD-MM-YYYY HH24:MI:SS') + DS_PENDING_JOB.SUBMIT_DATETIME/(24*60*60)+(7/24)), 'mm/dd/yy hh24:mi:ss'),
Decode(DS_PENDING_JOB.JOB_STATUS, 0, 'Success',1,'Failure',2,'Waiting',3,'Running',4,'Suspended',1001,'Retrying(1)',1002,'Retrying(2)',1003,'Retrying(3)',1004,'Retrying(4)',1005,'Retrying(5)',1006,'Expired','UNKNOWN'),
OBJ_M_DOCATVAR.M_SRC_C_NAME,
OBJ_M_DOCATVAR.M_DOCATV_N_NBROWS,
DS_PENDING_JOB.ERROR_TEXT
FROM
OBJ_M_ACTOR SUBMITING_USER,
OBJ_M_DOCUMENTS,
DS_PENDING_JOB,
OBJ_M_DOCATVAR
WHERE
( DS_PENDING_JOB.DOCUMENT_ID=OBJ_M_DOCUMENTS.M_DOC_N_ID )
AND ( SUBMITING_USER.M_ACTOR_N_ID=DS_PENDING_JOB.USER_SUBMIT_ID )
AND ( OBJ_M_DOCATVAR.M_DOC_N_ID=OBJ_M_DOCUMENTS.M_DOC_N_ID )
jswoboda (BOB member since 2002-06-20)
send away! the more reports I have to look at the better…thanks
and after we get our report up and going if any of you want a copy I will be more than happy to share it too!
LotusSutol (BOB member since 2003-06-30)
sent, let me know if you don’t get it for some reason. I have been having some email issues.
jswoboda (BOB member since 2002-06-20)
heres mine. hope it helps. i use this a free hand sql report connected to my repository.
select a.document_id,
d.M_DOC_C_NAME,
substr(b.M_ACTOR_C_NAME,1,10),
decode(a.job_status,0,'Success',1,'Failure',2,'Waiting',3,'Running',4,'Suspended','Retrying'),
substr(c.M_SRC_C_NAME,1,10),
a.start_datetime,
((to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') + (a.start_datetime / (60 * 60 * 24))) - 6/24),
a.end_datetime,
((to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') + (a.end_datetime / (60 * 60 * 24))) - 6/24),
((a.end_datetime - a.start_datetime) / 60 ) / 60
from DS_PENDING_JOB a, obj_m_actor b, OBJ_M_DOCATVAR c, OBJ_M_DOCUMENTS D
where a.USER_SUBMIT_ID = b.M_ACTOR_N_ID
and a.DOCUMENT_ID = c.M_DOC_N_ID
and a.document_id = d.M_DOC_N_ID
order by substr(c.M_SRC_C_NAME,1,10)jim_green_1 (BOB member since 2004-03-26)
got it and it works…I am making changes and adding some charts…after I am done if any of you want a copy…let me know. thanks again!
LotusSutol (BOB member since 2003-06-30)
jeffrey swoboda, I am using your universe (some mods to it)…and I am sooo close to having the trends chart complete! I have a line that shows the trend over a two week period for total BCA jobs ran for the day, total success, and total failure…what I am stuck on is getting a line showing the total scheduled for the day…
Anybody have a clue how to figure out how many are scheduled to run for the day?
LotusSutol (BOB member since 2003-06-30)
Michael, glad it helped. I would love to see the finished product.
Good question about the scheduling, I’ll take a look and see if I can help.
jswoboda (BOB member since 2002-06-20)
Is that in the downloads area? You mind sharing?
Cindy Clayton (BOB member since 2002-06-11)
Cindy, are you asking me about the Universe I sent Michael or the BCA stat/errors report he is building? I do not mind sharing my Universe or report (when I say mine I really inherited it about 4 years ago I am not entirely sure where it came from). I read the directions in the Download area but it makes reference to posting in the SDK forum and then mentioning to a moderator. This is really just a .unv file and corresponding report, if you think it could benefit others if we put it in the download area just let me know what I need to do and I would be happy to share. - thanks
jswoboda (BOB member since 2002-06-20)
also, when I am done I have no problem sharing the report/edited unv file that I did too…
LotusSutol (BOB member since 2003-06-30)
I’d just send the .unv and .reps as per the instructions and provide a bit of info on what it does and what it is for. I’ve got a .rep or two and maybe a .unv in the downloads area myself. My audit stuff I think. It is the perfect place for your stuff too.
Thanks!
Cindy Clayton (BOB member since 2002-06-11)
The number of scheduled reports to run variable is a pain
To get the number of scheduled reports for TODAY this seems like it works
count(DS_PENDING_JOB.BEGIN_DATE + DS_PENDING_JOB.START_DATETIME)
That takes the jobs that have ran today and adds them to the jobs that are scheduled to run today and gives me a count on them
GREAT! I have the number for today.
This cant look into the future and it cant look into the past (beyond today would = 0 and in the past would = just the DS_PENDING_JOB.START_DATETIME).
I could however take this value and write it into a file with the date. Then the report could grab this value and we could use it to have a history of what the number of scheduled reports should have been for those days.
I am spending more time on this than I had hoped…but it is one of those things that once you get thinking on it…you want to solve it…oh well…
LotusSutol (BOB member since 2003-06-30)
I posted some code a while back using the BITAND operator to determine the “clear text” translation of the scheduling. You can use this in combination with the Original Start Date and some Sysdate functions (to determine the day of the week as well as the # of weeks since it was scheduled).
If you can get me some sample rows, I can help out in this, but it’s been a while since I’ve looked at the DS_PENDING_JOB table in any detail.
digpen (BOB member since 2002-08-15)
Hi there all,
First of all, this universe is very handy! This forum has supplied so many useful gizmos!!
I’m trying to convert this universe it to SQL Server, I’m doing ok but being a novice at all things SQL I’m having trouble with the dates.
Can someone help me convert the following to MS SQL Server?
to_char((to_date(‘14-12-1970 11:00:00’, ‘DD-MM-YYYY HH24:MI:SS’) + dbo.DS_PENDING_JOB.SUBMIT_DATETIME/(246060)+(7/24)), ‘mm/dd/yy hh24:mi:ss’)
Any tips appreciated,
Cheers,
Pete
foshdafosh 
(BOB member since 2004-05-04)
I think you can use dateadd( , , ‘s’), but I don’t have access to SQLServer any more to check the syntax. 
Paul Williams (BOB member since 2002-07-10)