Hello
I have an issue while trying to query the bizdoc table from the webMethods TN schema.
My problem is the display of “doctimestamp” record : I typically use the following query
select to_char (DOCTIMESTAMP,‘YYYY/MM/DD HH24:MI:SS.FF’) as DOCTIMESTAMP from bizdoc where DOCID=‘54cfpp008peddb2400000iti’
The issue here is that it display me the UTC time which is not my exact timezone (depends of winter time UTC+1 or UTC+2).
Is there a possibility to retrieve the good display of this record ?
Sample :
result of the query : 2014/06/02 14:16:12.725
a simple select sysdate from dual at the same time : 2014/06/02 16:16:12.725
try this with Oracle:
select
CAST((from_tz(cast(DOCTIMESTAMP AS TIMESTAMP),‘GMT’) AT TIME ZONE ‘CET’) AS DATE)
from table;
1 Like
Hi,
Tong wang is right. Just adding another one.
-
SELECT FROM_TZ(CAST(TO_DATE(DOCTIMESTAMP,
‘YYYY-MM-DD HH:MI:SS.FF’) AS TIMESTAMP), ‘America/New_York’)
AT TIME ZONE ‘asia/calcutta’ “Converted Time”
FROM DUAL;
Sample output: 01-DEC-99 21.30.00.000000000 ASIA/CALCUTTA (Input: “1999-12-01 11:00:00”)
-
ALTER SESSION SET TIME_ZONE = ‘asia/calcutta’;
select CURRENT_TIMESTAMP from dual;
or
ALTER SESSION SET TIME_ZONE = '+5:30';
select CURRENT_TIMESTAMP from dual;
Hope this will help you.
Hello
It helped me ![:smiley: :smiley:](https://emoji.discourse-cdn.com/twitter/smiley.png?v=12)
I just have another question. Would it be possible to format the result using a to_char function ?
I tried it but have now a sql error (date format not recognizable)
EDIT : I found the solution
select
to_char (CAST((from_tz(cast(DOCTIMESTAMP AS TIMESTAMP),‘GMT’) AT TIME ZONE ‘CET’) AS TIMESTAMP) ,‘YYYY/MM/DD HH24:MI:SS.FF’)
from table;
In any cases thanks for your help both :!:
Hi Julien,
While you have got the detail that you are looking for, just want to add that, there are two built-in services which would retrieve the content of bizdoc by providing the document id if you want to use it for some processing.
wm.tn.doc:view, and wm.tn.doc:viewAs
My 2 cents…
-Senthil