Author |
Message
|
Edde |
Posted: Wed Nov 01, 2006 9:14 am Post subject: Problem with ESQL datetime conversion. |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
I have a strange result of datetime conversion.
ESQL code is
Code: |
SET MSGDATE = CAST(InMes.Q1:dbdate AS TIMESTAMP FORMAT 'yyyyMMddHHmmss'); |
InMes.Q1:dbdate has value '20061012120000'.
After executing this statement i have MSGDATE = '2006-10-12 11:00:00.000'
What's wrong? |
|
Back to top |
|
|
jefflowrey |
Posted: Wed Nov 01, 2006 9:37 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
The default pattern used for converting timestamps into strings? _________________ I am *not* the model of the modern major general. |
|
Back to top |
|
|
Edde |
Posted: Wed Nov 01, 2006 10:08 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
I have business object generated using Object Discovery Agent.
And it defines datetime field from MS SQL Server as xsd:string.
So i have string representation of datetime from JDBC Adapter. |
|
Back to top |
|
|
jefflowrey |
Posted: Wed Nov 01, 2006 10:15 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
What is showing the value as
Quote: |
MSGDATE = '2006-10-12 11:00:00.000' |
?
A trace node?
The debugger?
The SQL database?
User Trace? _________________ I am *not* the model of the modern major general.
Last edited by jefflowrey on Wed Nov 01, 2006 1:34 pm; edited 1 time in total |
|
Back to top |
|
|
Edde |
Posted: Wed Nov 01, 2006 10:19 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
|
Back to top |
|
|
kimbert |
Posted: Wed Nov 01, 2006 1:32 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003 Posts: 5542 Location: Southampton
|
Quote: |
InMes.Q1:dbdate has value '20061012120000'. |
Let's make really sure of that. Write a snippet of ESQL like this
Code: |
DECLARE dateStr CHARACTER '20061012120000';
DECLARE MSGDATE TIMESTAMP;
SET MSGDATE = CAST(dateStr AS TIMESTAMP FORMAT 'yyyyMMddHHmmss'); |
If you still see the wrong results in MSGDATE then we need to investigate. |
|
Back to top |
|
|
Edde |
Posted: Thu Nov 02, 2006 12:07 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
kimbert wrote: |
Quote: |
InMes.Q1:dbdate has value '20061012120000'. |
Let's make really sure of that. Write a snippet of ESQL like this
Code: |
DECLARE dateStr CHARACTER '20061012120000';
DECLARE MSGDATE TIMESTAMP;
SET MSGDATE = CAST(dateStr AS TIMESTAMP FORMAT 'yyyyMMddHHmmss'); |
If you still see the wrong results in MSGDATE then we need to investigate. |
I tried with this code:
Code: |
DECLARE MSGDATE TIMESTAMP;
DECLARE MSGDATE1 TIMESTAMP;
DECLARE MSGDATE2 TIMESTAMP;
DECLARE dateStr CHARACTER '20061012120000';
DECLARE dateStr1 CHARACTER '20061012003000';
DECLARE dateStr2 CHARACTER '2006-10-12 12:00:00';
SET MSGDATE = CAST(dateStr AS TIMESTAMP FORMAT 'yyyyMMddHHmmss');
SET MSGDATE1 = CAST(dateStr1 AS TIMESTAMP FORMAT 'yyyyMMddHHmmss');
SET MSGDATE2 = CAST(dateStr2 AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss'); |
In debugger i see:
MSGDATE = 2006-10-12 11:00:00.000
MSGDATE1 = 2006-10-11 23:30:00.000
MSGDATE2 = 2006-10-12 11:00:00.000
It looks like daylight saving option or timezone conversion.
But i can't find in documentation what's it is. |
|
Back to top |
|
|
jefflowrey |
Posted: Thu Nov 02, 2006 2:38 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
Your output values are timestamps. Timestamps are "unformatted". The debugger is showing you the value of the timestamp after it has been converted to a character value.
Try adding
Code: |
declare dateStr3 CHARACTER;
SET dateStr3 = (CAST dateStr1 as CHARACTER format 'yyyMMddHHmmss'); |
_________________ I am *not* the model of the modern major general. |
|
Back to top |
|
|
Edde |
Posted: Thu Nov 02, 2006 2:44 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
jefflowrey wrote: |
Your output values are timestamps. Timestamps are "unformatted". The debugger is showing you the value of the timestamp after it has been converted to a character value.
Try adding
Code: |
declare dateStr3 CHARACTER;
SET dateStr3 = (CAST dateStr1 as CHARACTER format 'yyyMMddHHmmss'); |
|
As i know when casting CHAR to CHAR FORMAT is ignored.
You mean
Code: |
declare dateStr3 CHARACTER;
SET dateStr3 = (CAST MSGDATE1 as CHARACTER format 'yyyMMddHHmmss'); |
? |
|
Back to top |
|
|
jefflowrey |
Posted: Thu Nov 02, 2006 4:20 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
Yes. _________________ I am *not* the model of the modern major general. |
|
Back to top |
|
|
Edde |
Posted: Thu Nov 02, 2006 5:04 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
dateStr3 = 061011233000
I think this can be the system problem rather than broker problem.
But i can't even surmise how to repair it |
|
Back to top |
|
|
jefflowrey |
Posted: Thu Nov 02, 2006 6:07 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
It's a display "problem".
It's just how the debugger is SHOWING the value. _________________ I am *not* the model of the modern major general. |
|
Back to top |
|
|
Edde |
Posted: Thu Nov 02, 2006 6:13 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006 Posts: 67 Location: Moscow, Russia
|
jefflowrey wrote: |
It's a display "problem".
It's just how the debugger is SHOWING the value. |
Code: |
DECLARE MSGDATE1 TIMESTAMP;
DECLARE dateStr1 CHARACTER '20061012003000';
DECLARE dateStr3 CHARACTER;
SET MSGDATE1 = CAST(dateStr1 AS TIMESTAMP FORMAT 'yyyyMMddHHmmss');
SET dateStr3 = CAST(MSGDATE1 AS CHARACTER format 'yyyMMddHHmmss'); |
Debugger shows dateStr3 = 061011233000.
You think debugger shows incorrectly a character value? |
|
Back to top |
|
|
jefflowrey |
Posted: Thu Nov 02, 2006 6:16 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002 Posts: 19981
|
It's just not my day.
_________________ I am *not* the model of the modern major general. |
|
Back to top |
|
|
fjb_saper |
Posted: Thu Nov 02, 2006 4:07 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003 Posts: 20729 Location: LI,NY
|
Have you checked the timezone on the box...
see for unix using the broker service user:
date
echo ${TZ}
Add as well the output of
mqsiservice -t
(from memory...the option about time and timezone)
Then tell us whether the date and time displayed from the date command are correct.
_________________ MQ & Broker admin |
|
Back to top |
|
|
|