UTC Date Format and DAS

scheduling Broadcast Agent (BCA)
1

I am trying to come up with a SQL Statement that will allow me to produce a report showing the status of reports that have been run by the DAS (SQL 7, BO 4.1.5).

I have found that the UTC definition that Business Objects has provided does not match with what I have seen. When I calculate the number of seconds since December 15th, 1970 at midnight, I am off by 5 hours and have to adjust my query accordingly. Furthermore I have found that when a document has not yet finished (either waiting or running) then the date is bumped ahead by 37 years.
I am very confused as to what is happening on the back end. I know that there has to be some sort of logical explanation, because when you look at the log for the Document Agent Server then everything is calculated properly.

Below is the query that I am using. Any ideas what more I can do? Why am I off by 5 hours for completed jobs? Why am I off by 37 years for non-completed jobs? The information that I used regarding the repository and UTC date format was found on the CD at K:\freeware\Repostry\RPDSPJ.HTM.

Thank you in advance,
Simon Miller
Information Analyst
I.T. North America
DiverseyLever

select
right(obj_m_documents.m_doc_c_name,45),
DATEADD(second,(convert(decimal,(ds_pending_job.SUBMIT_DATETIME))),‘1970-12-14 19:00:00.000’) ‘Submit Date/Time’,
DATEADD(second,(convert(decimal,(ds_pending_job.START_DATETIME))),‘1970-12-14 19:00:00.000’) ‘Start Date/Time’,
DATEADD(second,(convert(decimal,(ds_pending_job.END_DATETIME))),‘1970-12-14 19:00:00.000’) ‘End Date/Time’,
ds_pending_job.JOB_STATUS,
ds_pending_job.JOB_ERROR,
ds_pending_job.ERROR_TEXT
from
ds_pending_job,
obj_m_documents
where ds_pending_job.document_id = obj_m_documents.m_doc_n_id

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 1/4/00 3:24:56 PM Mountain Standard Time, Simon.Miller@DIVERSEYLEVER.COM writes:

When I calculate the number of seconds since
December 15th, 1970 at midnight, I am off by 5 hours and have to adjust my query accordingly. Furthermore I have found that when a document has not yet
finished (either waiting or running) then the date is bumped ahead by 37 years.

I’m not sure I can explain the second part, but I can explain the first part. Dates (and times) are stored in the repository in GMT. You will need to add (or subtract) based on your time zone. Obviously when you access the repository via DAS Console the Time Zone variable is being used. You should do the same if you access the repository data directly.

If you think about it, this is a “good thing”. It means that reports can be submitted from any time zone (for a company that has offices in different time zones) and they all get converted to a standard time (GMT). It does not matter which time zone the DAS Server is in, nor which time zone the submitter is in.

There used to be a bug in 4.1.something where it would work correctly on Win 95, but not work (meaning show incorrect times in the DAS Console) under Win NT. It had to do with the automatic daylight savings time settings. I think that has been fixed.

So that’s an answer for at least 1/2 of the question; I’ll leave it to someone else to try to explain the 37 year difference on non-executed documents. :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

3

select
right(obj_m_documents.m_doc_c_name,45), DATEADD(second,(convert(decimal,(ds_pending_job.SUBMIT_DATETIME))),‘1970 -12-14
19:00:00.000’) ‘Submit Date/Time’,
DATEADD(second,(convert(decimal,(ds_pending_job.START_DATETIME))),‘1970- 12-14
19:00:00.000’) ‘Start Date/Time’,
DATEADD(second,(convert(decimal,(ds_pending_job.END_DATETIME))),‘1970-12 -14
19:00:00.000’) ‘End Date/Time’,

Seconded - docs are a bit rubbish to say the least. I use the following function to return the correct date/time (it’s in oracle format, so convert at will):

to_date(‘15-12-1970 00:00:00’,‘dd-mm-yyyy hh24:mm:ss’) + (ds_pending_job. / 86399.656)

hope this helps.

Regards

Brian Patterson

Listserv Archives (BOB member since 2002-06-25)

4

Thanks Dave,
It was so obvious that I would never have figured it out! Simon

Listserv Archives (BOB member since 2002-06-25)