ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ISO8601 Timestamp Conversion

Post new topic  Reply to topic
 ISO8601 Timestamp Conversion « View previous topic :: View next topic » 
Author Message
jayZ
PostPosted: Tue Jul 16, 2013 12:24 pm    Post subject: ISO8601 Timestamp Conversion Reply with quote

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
View user's profile Send private message
smdavies99
PostPosted: Tue Jul 16, 2013 10:12 pm    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Tue Jul 16, 2013 11:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
rekarm01
PostPosted: Wed Jul 17, 2013 3:41 am    Post subject: Re: ISO8601 Timestamp Conversion Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ISO8601 Timestamp Conversion
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.