system
June 30, 2004, 9:56am
1
When I fire the following query on DS_PENDING_JOB:
SELECT
OBJ_M_DOCUMENTS_DS.M_DOC_C_NAME,
(to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +(
DS_PENDING_JOB.START_DATETIME / (60 * 60 * 24))) SDT,
(to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +(
DS_PENDING_JOB.END_DATETIME / (60 * 60 * 24))) EDT,
(to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +(
DS_PENDING_JOB.EXPIRATION_DATE / (60 * 60 * 24))) ED,
(to_date('15/12/1970 00:00:00', 'DD/MM/YYYY HH24:MI:SS') +(
DS_PENDING_JOB.SUBMIT_DATETIME / (60 * 60 * 24))) SUBDT
FROM
OBJ_M_DOCUMENTS OBJ_M_DOCUMENTS_DS,
DS_PENDING_JOB,
DS_USER_LIST
WHERE
( DS_USER_LIST.DOCUMENT_ID=DS_PENDING_JOB.DOCUMENT_ID )
AND ( DS_USER_LIST.DOCUMENT_ID=OBJ_M_DOCUMENTS_DS.M_DOC_N_ID )
I get the following values:
M_DOC_C_NAME Document1
START_DATETIME 06/02/2037 06:28
END_DATETIME 06/02/2037 06:28
EXPIRATION_DATE 30/06/2005 08:48
SUBDT 30/06/2004 08:50
As you can see, the value for start datetime and end datetime are not what I would expect. Can you help me see whats wrong?
[edited, used bbc for better readability - Andreas]
subodhgupta (BOB member since 2004-06-22)
system
June 30, 2004, 10:22am
2
The dates are stored in UTC format, try this link
Nick Daniels (BOB member since 2002-08-15)
system
June 30, 2004, 10:28am
3
Hi Nick,
I do understand that the dates are in UTC. And that the reason why I have added the to_date(‘15/12/1970 00:00:00’, ‘DD/MM/YYYY HH24:MI:SS’) to all the dates. However, as you would notice, the start datetime and the end datetime are out by 33 years!!
Can you help me with it.
subodhgupta (BOB member since 2004-06-22)
system
June 30, 2004, 10:29am
4
Oh sorry. Stupid question, but did you check document1, just to ensure it doesn’t have those actual dates on it? What about other documents?
Nick Daniels (BOB member since 2002-08-15)
system
June 30, 2004, 10:32am
5
The dates are out only for document in Waiting Status. The rest are fine.
subodhgupta (BOB member since 2004-06-22)
system
June 30, 2004, 1:22pm
6
Those are essentially telling you that the document has not yet been executed. Instead of leaving the columns blank, they store those numbers instead. You can safely ignore them, or use a CASE or DECODE() function to translate them to some other value if you like.
Dave Rathbun (BOB member since 2002-06-06)
system
June 30, 2004, 3:18pm
7
Thanks for your inputs Dave. Just one more question. Is that default date which is appearing constant.
subodhgupta (BOB member since 2004-06-22)
For what I’ve seen it’s pretty constant. They have to change that in some future version though (or there will be a real 2037 problem ).
I use this piece of SQL in my job to check the BCA:
Decode(submit_datetime, 2087447296, Null, to_date(‘1970349000000’,‘yyyydddhh24miss’) + nvl(submit_datetime,0)/86400)
HenkK
HenkK (BOB member since 2004-03-02)