|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
 |
|
ISO8601 Timestamp Conversion |
« View previous topic :: View next topic » |
Author |
Message
|
jayZ |
Posted: Tue Jul 16, 2013 12:24 pm Post subject: ISO8601 Timestamp Conversion |
|
|
Acolyte
Joined: 03 Jun 2008 Posts: 71
|
I am working on a message flow which receives an XML document via FileInput, first in the blob domain (because the flow processes different formats) and then into the XMLNSC domain.
Code: |
CREATE LASTCHILD OF OutputLocalEnvironment DOMAIN('XMLNSC') PARSE(InputRoot.BLOB.BLOB, InputRoot.Properties.Encoding,InputRoot.Properties.CodedCharSetId); |
The document looks like this:
Code: |
<row>
<value>ARKL1</value>
<value>2013-05-21T00:00:00</value>
<value>773</value>
<value>893</value>
<value>75.66623545</value>
</row>
|
The destination expects a date format of yyyy-MM-dd HH:mm:ss.
My problem is, every way to try to reformat that date value results in a runtime error. Even attempts to modify the data have failed.
Here is what I've tried...
Based on other threads on this topic, I've tried changing the data to 2013-05-21T00:00:00.000, 2013-05-21T00:00:00.000000 and 2013-05-21T01:23:12.123456 in order to be more in line with the ISO8601 standard.
I've tried the following code (there are more elements to the message than what I've posted. My problem is not navigating to the correct element):
Simple CHAR to TIMESTAMP CAST. Would then CAST back to CHAR with FORMAT = yyyy-MM-dd HH:mm:ss. Fails on the TIMESTAMP CAST, though.
Code: |
DECLARE dt CHAR OutputLocalEnvironment.XMLNSC.NS:dataset.NS:data.NS:row[1].NS:value[2];
DECLARE dateS TIMESTAMP;
SET dateS = CAST(dt AS TIMESTAMP);
|
Same CAST, but define the format to force broker to accept the 'T'
Code: |
DECLARE dt CHAR OutputLocalEnvironment.XMLNSC.NS:dataset.NS:data.NS:row[1].NS:value[2];
DECLARE dateS TIMESTAMP;
SET dateS = CAST(dt AS TIMESTAMP FORMAT 'yyyy-MM-ddTHH:mm:ss');
|
Same thing but with I, as mentioned in another thread and infocenter
Code: |
DECLARE dt CHAR OutputLocalEnvironment.XMLNSC.NS:dataset.NS:data.NS:row[1].NS:value[2];
DECLARE dateS TIMESTAMP;
SET dateS = CAST(dt AS TIMESTAMP FORMAT 'yyyy-MM-ddIHH:mm:ss');
|
Tried without the timestamp cast
Code: |
CREATE LASTCHILD OF OutputRoot.JSON.Data.Item[batchCount] TYPE NameValue NAME 'inventory_dt' VALUE CAST(OutputLocalEnvironment.XMLNSC.NS:dataset.NS:data.NS:row[1].NS:value[2] AS CHAR FORMAT 'yyyy-MM-dd HH:mm:ss'); |
Every time, I get an error complaining about the T. I know I can just read this as a string and do a replace T with '', but I was really hoping to use the correct data type.
Can anyone help me get from string: 2013-05-21T00:00:00 to string: 2013-05-21 00:00:00 using a CAST functions instead of replace. I'm worried that using replace could mess up the formatting and cause problems in the service I'm calling. |
|
Back to top |
|
 |
smdavies99 |
Posted: Tue Jul 16, 2013 10:12 pm Post subject: |
|
|
 Jedi Council
Joined: 10 Feb 2003 Posts: 6076 Location: Somewhere over the Rainbow this side of Never-never land.
|
Did you search for posts on this forum that contain the following?
yyyy-MM-ddIHH:mm:ss
Might be worth a try _________________ WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
Back to top |
|
 |
mqjeff |
Posted: Tue Jul 16, 2013 11:45 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
That's not a valid format for an XML date field. If your schema specifies that this field is a date, then the document you have shown is not valid according to that schema.
If your schema says that this field is a string, then you merely need to use normal ESQL CASTs, with appropriate format information, to convert it to and from a timestamp.
You should really make sure that the field is a date in the XML schema and that the sending application validates the XML document against that schema before it sends the message. Contracts are a good thing. |
|
Back to top |
|
 |
rekarm01 |
Posted: Wed Jul 17, 2013 3:41 am Post subject: Re: ISO8601 Timestamp Conversion |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 1415
|
jayZ wrote: |
Same CAST, but define the format to force broker to accept the 'T'
Code: |
DECLARE dt CHAR OutputLocalEnvironment.XMLNSC.NS:dataset.NS:data.NS:row[1].NS:value[2];
DECLARE dateS TIMESTAMP;
SET dateS = CAST(dt AS TIMESTAMP FORMAT 'yyyy-MM-ddTHH:mm:ss');
|
|
The literal 'T' needs single quotes in the format:
Code: |
SET dateS = CAST(dt AS TIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ss'); |
|
|
Back to top |
|
 |
|
|
 |
|
Page 1 of 1 |
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|
|