| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| String to DATE and TIMESTAMP conversion |
« View previous topic :: View next topic » |
| Author |
Message
|
| mqxplorer |
 Posted: Thu Feb 10, 2011 7:58 am Post subject: String to DATE and TIMESTAMP conversion |
 |
|
Master
Joined: 22 Jun 2009
Posts: 206
|
Hi All,
I have a question regarding the string to DATE and TIMESTAMP conversion.
I have a simple message flow which handles the CSV messages. In the message set, I have an element named requestDate of data type 'xsd:string'. This is supposed to be of type 'xsd:date' or 'xsd:dateTime'. I have purposefully made its type to 'xsd:string' just to test the String to DATE and DATETIME scenarios.
When the incoming data is as below:
| Code: |
HEADER,2002-05-30,X123456IB7,customer details
|
I have the below code to convert the incoming string type date to DATE data type:
| Code: |
DECLARE resDate DATE;
SET resDate = CAST(InputRoot.MRM.header.requestDate AS DATE CCSID 1208); |
This successfuuly converts the string date to DATE type date
| Code: |
| requestDate:CHARACTER:2002-05-30 |
| Code: |
resDate:DATE:java.util.GregorianCalendar[time=1022731200000,areFieldsSet=true,
areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/New_York",
offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,
lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,
dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,
startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,
endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],
firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=?,YEAR=2002,MONTH=4,WEEK_OF_YEAR=?,
WEEK_OF_MONTH=?,DAY_OF_MONTH=30,DAY_OF_YEAR=?,DAY_OF_WEEK=?,
DAY_OF_WEEK_IN_MONTH=?,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,
SECOND=0,MILLISECOND=?,ZONE_OFFSET=?,DST_OFFSET=?] |
This is good. However, when I am sedning the incoming data as below:
| Code: |
| HEADER,2002-05-30T09:00:00Z,X123456IB7,customer details |
by making the requestDate field length to 20 and try to convert it to TIMESTAMP data type, I am getting the exception.
Below is the code to convert the incoming string type date to TIEMSTAMP data type:
DECLARE resDateTime TIMESTAMP;
SET resDateTime = CAST(InputRoot.MRM.header.requestDate AS TIMESTAMP CCSID 1208);
| Code: |
| requestDate:CHARACTER:2002-05-30T09:00:00Z |
Exception is:
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:CSV2XML#FCMComposite_1_3
Label:CHARACTER:CSV2XML.CSV2XML
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:CSV2XML#FCMComposite_1_3
Label:CHARACTER:CSV2XML.CSV2XML
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.CSV2XML_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:11.3
Insert
Type:INTEGER:5
Text:CHARACTER:SET resDateTime = CAST(InputRoot.MRM.header.requestDate AS TIMESTAMP CCSID 1208);
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlTypeCast.cpp
Line:INTEGER:260
Function:CHARACTER:SqlTypeCast::evaluate
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2521
Text:CHARACTER:Error while casting
Insert
Type:INTEGER:5
Text:CHARACTER:.CSV2XML_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:11.21
Insert
Type:INTEGER:5
Text:CHARACTER:'2002-05-30T09:00:00Z'
Insert
Type:INTEGER:5
Text:CHARACTER:TIMESTAMP
CastException
File:CHARACTER:F:\build\S700_P\src\CommonServices\ImbDateTime.cpp
Line:INTEGER:2797
Function:CHARACTER:ImbTimeStamp::fromString
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2461
Text:CHARACTER:Invalid timestamp string
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:5
Text:CHARACTER:S22007
Insert
Type:INTEGER:5
Text:CHARACTER:2002-05-30T09:00:00Z |
Why can one type conversion work and why not the other one...
Please throw some light on this....
Thanks
mqxplorer |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Feb 10, 2011 8:05 am Post subject: Re: String to DATE and TIMESTAMP conversion |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqxplorer wrote: |
| Please throw some light on this.... |
Does it work if you specify the IU format?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqxplorer |
| Posted: Thu Feb 10, 2011 11:29 am Post subject: |
|
|
Master
Joined: 22 Jun 2009
Posts: 206
|
Hi Vitor,
It worked using the below code...
| Code: |
DECLARE resDateTime TIMESTAMP;
SET resDateTime = CAST(InputRoot.MRM.header.requestDate AS TIMESTAMP FORMAT 'IU'); |
The incmong data value is:
| Code: |
| requestDate:CHARACTER:2002-05-30T09:00:00Z |
The resulting data value after casting :
| Code: |
| 2002-05-30 05:00:00.000 |
while casting, 4 hours were deducted from the incoming date data (9-4 = 5). If the audit mechanism provided by WMBV6.1 and above and if we audit this particular element, the date of the incmoming date will be different from the message sent out to the backend application.
Actually, the reason to start this thread is just a continuation to my original post regarding auditing events in the below link. I just did not want to cinfuse people by posting this question in that long thread.
http://www.mqseries.net/phpBB2/viewtopic.php?t=55888&postdays=0&postorder=asc&start=0
I am capturing the payload using the below code in my audit flow.
I have specfied $Body in the monitoring event tab for the payload and it is generating exceptions for MRM domain messages with incoming data of dataTime value.
When the incoming data field is let's say 'reqDate' of type 'xsd:dateTime' and has a value of '2002-05-30T09:00:00Z' and broker logical tree structure has the values as below.
| Code: |
| requestDate 2002-05-30 09:00:00.000 |
| Code: |
requestDate:TIMESTAMP:java.util.GregorianCalendar
[time=1022763600000,areFieldsSet=true,areAllFieldsSet=false,lenient=true,
zone=sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,
dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone
[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,
startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,
startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,
endDayOfWeek=1,endTime=7200000,endTimeMode=0]],
firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=?,YEAR=2002,MONTH=4,
WEEK_OF_YEAR=?,WEEK_OF_MONTH=?,DAY_OF_MONTH=30,DAY_OF_YEAR=?,
DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=0,HOUR=9,HOUR_OF_DAY=9,
MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=?,DST_OFFSET=?] |
When the audit event is generated the part of the payload is as below:
| Code: |
<wmb:complexContent wmb:elementName="MRM">
<MRM>
<header>
<requestDate>2002-05-30T09:00:00Z</requestDate>
<requestID>X123456IB7</requestID>
<requestType>customer details</requestType>
</header>
</MRM>
</wmb:complexContent> |
and the requestDate shown above is a string type as per the audit schema provided by IBM.
I have the below code to handle the payload part in MRM domain.
| Code: |
IF Environment.Content[I].name = 'Properties' THEN
SET OutputRoot.Properties = contentRef.*[<];
ELSEIF Environment.Content[I].name IN ('MRM', 'XMLNSC', 'SOAP', 'DataObject', 'XMLNS', 'JMSMap', 'JMSStream', 'MIME', 'BLOB') THEN
DECLARE chkDomain CHARACTER;
SET chkDomain = FIELDVALUE(Environment.Content[I].name);
CASE chkDomain
WHEN 'MRM' THEN
CREATE LASTCHILD OF OutputRoot DOMAIN 'MRM';
SET OutputRoot.MRM = contentRef.*[<];
DECLARE propRef REFERENCE TO OutputRoot.Properties;
DECLARE inCCSID INT propRef.CodedCharSetId;
DECLARE inEncoding INT propRef.Encoding;
DECLARE msgSet CHARACTER propRef.MessageSet;
DECLARE msgType CHARACTER propRef.MessageType;
DECLARE msgFormat CHARACTER propRef.MessageFormat;
DECLARE msgBitStream BLOB ASBITSTREAM(OutputRoot.MRM, inEncoding, inCCSID, msgSet, msgType, msgFormat);
SET msgChar = CAST(msgBitStream AS CHAR CCSID inCCSID); |
The reason to execute the statement
SET msgChar = CAST(msgBitStream AS CHAR CCSID inCCSID)
is - I am puting the payload as character type in the database of column type 'ntext'.
The code DECLARE msgBitStream BLOB ASBITSTREAM(OutputRoot.MRM, inEncoding, inCCSID, msgSet, msgType, msgFormat);
is genearting the below exception.
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_1
Label:CHARACTER:Audit_Flow_Local_Payload.AuditEvent_Queue
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_2
Label:CHARACTER:Audit_Flow_Local_Payload.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_2
Label:CHARACTER:Audit_Flow_Local_Payload.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.Audit_Flow_Local_Payload_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:46.3
Insert
Type:INTEGER:5
Text:CHARACTER:ContentCount(appDataRef, payLoad);
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:628
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_2
Label:CHARACTER:Audit_Flow_Local_Payload.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:ContentCount
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_2
Label:CHARACTER:Audit_Flow_Local_Payload.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.Audit_Flow_Local_Payload_Compute.ContentCount
Insert
Type:INTEGER:5
Text:CHARACTER:36.7
Insert
Type:INTEGER:5
Text:CHARACTER:DECLARE msgBitStream BLOB ASBITSTREAM(OutputRoot.MRM ENCODING inEncoding CCSID inCCSID SET msgSet TYPE msgType FORMAT msgFormat);
ParserException
File:CHARACTER:F:\build\S700_P\src\MTI\MTIforBroker\MtiImbParser2\MtiImbParser.cpp
Line:INTEGER:1872
Function:CHARACTER:MtiImbParser::refreshBitStreamFromElements - 9 par
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:Audit_Flow_Local_Payload#FCMComposite_1_1
Label:CHARACTER:Audit_Flow_Local_Payload.AuditEvent_Queue
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:5286
Text:CHARACTER:ImbRecoverableException caught from worker when attempting to write out the bitstream.
Insert
Type:INTEGER:5
Text:CHARACTER:CSV
Insert
Type:INTEGER:2
Text:CHARACTER:1
Insert
Type:INTEGER:5
Text:CHARACTER:CSV
Insert
Type:INTEGER:5
Text:CHARACTER:/CSV_5
ParserException
File:CHARACTER:F:\build\S700_P\src\cpi\pwf\nxd\tdswriter.cpp
Line:INTEGER:272
Function:CHARACTER:CTDSWriter::write
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:5447
Text:CHARACTER:TDS writing error
Insert
Type:INTEGER:5
Text:CHARACTER:/CSV_5/header/requestDate
ParserException
File:CHARACTER:F:\build\S700_P\src\MTI\MTIforBroker\MtiImbParser2\MtiImbValueValidator.cpp
Line:INTEGER:1865
Function:CHARACTER:MtiImbValueValidator::basicValidate
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:5164
Text:CHARACTER:Exception thrown when casting to the expected logical type
Insert
Type:INTEGER:5
Text:CHARACTER:requestDate
Insert
Type:INTEGER:5
Text:CHARACTER:DATETIME
Insert
Type:INTEGER:5
Text:CHARACTER:STRING
Insert
Type:INTEGER:5
Text:CHARACTER:2002-05-30T09:00:00Z
CastException
File:CHARACTER:F:\build\S700_P\src\CommonServices\ImbDateTime.cpp
Line:INTEGER:2797
Function:CHARACTER:ImbTimeStamp::fromString
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2461
Text:CHARACTER:Invalid timestamp string
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:5
Text:CHARACTER:S22007
Insert
Type:INTEGER:5
Text:CHARACTER:2002-05-30T09:00:00Z
|
As per my analysis, this is because the broker does not support the implicit string to TIMESTAMP type convesrion which is not a problem from string to DATE type.
Is this a bug in the product?
Thanks
mqxplorer[/b] |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Feb 10, 2011 8:50 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Well I do know that TIMESTAMP has some challenges. You'd probably be better off holding all your values in GMTTIMESTAMP.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqxplorer |
| Posted: Fri Feb 11, 2011 8:50 am Post subject: |
|
|
Master
Joined: 22 Jun 2009
Posts: 206
|
@fjb_saper,
| Quote: |
Well I do know that TIMESTAMP has some challenges. You'd probably be better off holding all your values in GMTTIMESTAMP.
|
Ok. that is good.
Could you answer my question regarding the implicit string type dateTime value to TIMESTAMP conevrsion? As my first post says - implicit string type date value to DATE conversion happens without any comaplaints. Why can't it be the same behavior with string type dateTime value? Is it a bug?
As my second lengthy answer explains the issue with my custom audit flow....it would be really helpful, if you could answer my question regarding the audit.
Thanks
mqxplorer |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Feb 12, 2011 5:34 pm Post subject: Re: String to DATE and TIMESTAMP conversion |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| mqxplorer wrote: |
| I have a question regarding the string to DATE and TIMESTAMP conversion. |
The WMB InfoCenter is a useful resource for answering questions regarding the string to DATE and TIMESTAMP conversion.
| mqxplorer wrote: |
| Code: |
| SET resDate = CAST(InputRoot.MRM.header.requestDate AS DATE CCSID 1208); |
|
The CCSID parameter is only useful for CASTing to/from BIT or BLOB.
| mqxplorer wrote: |
This successfuuly converts the string date to DATE type date
| Code: |
| requestDate:CHARACTER:2002-05-30 |
|
Really? How well does this work after 8 PM? Is it off by one day?
| mqxplorer wrote: |
| Code: |
| SET resDateTime = CAST(InputRoot.MRM.header.requestDate AS TIMESTAMP CCSID 1208); |
| Code: |
CastException
Number:INTEGER:2461
Text:CHARACTER:Invalid timestamp string
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:5
Text:CHARACTER:S22007
Insert
Type:INTEGER:5
Text:CHARACTER:2002-05-30T09:00:00Z |
|
The WMB InfoCenter is a useful resource for explaining BIP error codes, SQL states, and how to resolve user errors:
| Quote: |
BIP2461: Error casting character string '2002-05-30T09:00:00Z' to a TIMESTAMP. State = -1 'S22007' '0'
Explanation: The character string must have a the form 'TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.f+]'', or 'yyyy-mm-dd hh:mm:ss[.f+]' to be valid for casting to a timestamp.
Response: Ensure that the string represents a valid time value. |
| Quote: |
| SqlState = 'S22007': Date time format not valid. A character string used in a cast from character to a datetime type had either the wrong basic format (for example, '01947-10-24') or had values outside the ranges allowed by the Gregorian calendar (for example, '1947-21-24'). |
Either provide a character string that matches the expected format, or provide a FORMAT parameter that matches the given string.
| mqxplorer wrote: |
| Is this a bug in the product? |
No, not in the product.
| mqxplorer wrote: |
| Code: |
| SET resDateTime = CAST(InputRoot.MRM.header.requestDate AS TIMESTAMP FORMAT 'IU'); |
while casting, 4 hours were deducted from the incoming date ... |
... as it should, when converting from GMT to EDT. To avoid converting dates and times to the local timezone, CAST to GMTDATE or GMTTIMESTAMP instead. |
|
| 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
|
|
|
|
