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 IndexWebSphere Message Broker SupportCAST from CHAR to DATE failing in v10.0.0.5

Post new topicReply to topic
CAST from CHAR to DATE failing in v10.0.0.5 View previous topic :: View next topic
Author Message
mindspace
PostPosted: Mon May 01, 2017 2:09 pm Post subject: CAST from CHAR to DATE failing in v10.0.0.5 Reply with quote

Novice

Joined: 22 Jan 2013
Posts: 14

I see strange issue with Cast function from CHAR to DATE, I have input JSON and transforming it to ISO using DFDL ISO model.
Please see trace info below, the issue seems to be happening only if the current date is 30 April and input message has CC expiry date with month as 02 I tried tests with current date as 28 April , 1 May and all worked fine.

Success Case

Test done in my Laptop with current date as 01 May
CC expiry date in JSON message is 2002(yyMM)

2017-05-01 17:27:25.011166 17040 UserTrace BIP2539I: Node '': Evaluating expression ''MyJSONTran.expdate'' at ('.Base24_TransformToISOAndStoreCorrel.Main', '175.41'). This resolved to ''MyJSONTran.expdate''. The result was '''2002'''.
2017-05-01 17:27:25.011280 17040 UserTrace BIP2539I: Node '': Evaluating expression ''CAST(MyJSONTran.expdate AS DATE FORMAT 'yyMM')'' at ('.Base24_TransformToISOAndStoreCorrel.Main', '175.36'). This resolved to ''CAST('2002' AS DATE FORMAT 'yyMM' )''. The result was ''DATE '2020-02-01'''.
2017-05-01 17:27:25.011308 17040 UserTrace BIP2566I: Node 'JSONToBase24.JSONToBase24Request.Transform From JSON and Store CorrelId': Assigning value ''DATE '2020-02-01''' to field / variable ''OutISO.DateExpiration_014''.

Failure Case

Test done in my Laptop with current date as 30 April
CC expiry date in JSON message is 2002(yyMM)

2017-04-30 17:20:17.803268 8616 UserTrace BIP2537I: Node 'JSONToBase24.JSONToBase24Request.Transform From JSON and Store CorrelId': Executing statement ''SET OutISO.DateExpiration_014 = CAST(MyJSONTran.expdate AS DATE FORMAT 'yyMM');'' at ('.Base24_TransformToISOAndStoreCorrel.Main', '175.4').
2017-04-30 17:20:17.803276 8616 UserTrace BIP2539I: Node '': Evaluating expression ''MyJSONTran.expdate'' at ('.Base24_TransformToISOAndStoreCorrel.Main', '175.41'). This resolved to ''MyJSONTran.expdate''. The result was '''2002'''.
2017-04-30 17:20:23.701330 8616 RecoverableException BIP2488E: ('.Base24_TransformToISOAndStoreCorrel.Main', '175.4') Error detected whilst executing the SQL statement ''SET OutISO.DateExpiration_014 = CAST(MyJSONTran.expdate AS DATE FORMAT 'yyMM');''.
2017-04-30 17:20:23.701336 8616 RecoverableException BIP2521E: ('.Base24_TransformToISOAndStoreCorrel.Main', '175.36') : Error casting the value '''2002''' to ''DATE''.
An error occurred when casting a value to a different data type. This may be because no conversions exist between the two data types or because the particular value was unsuitable.
Subsequent messages will indicate the context of the error.
2017-04-30 17:20:23.701340 8616 CastException BIP2327E: Error casting '2020' years '2' months '30' days to a DATE. State = '-1' ''S22007'' '0' ''
Numbers may only be converted to a date if they conform to the constraints of the Gregorian calendar.
Ensure that the values being cast have valid values.

DFDL Format

<annotation>
<appinfo source="http://www.ogf.org/dfdl/">
<dfdl:defineFormat name="ISO8583Format">
<dfdl:format alignment="1" alignmentUnits="bytes" binaryFloatRep="ieee" byteOrder="bigEndian" calendarCenturyStart="53" calendarCheckPolicy="lax" calendarDaysInFirstWeek="4" calendarFirstDayOfWeek="Monday" calendarLanguage="en-US" calendarObserveDST="yes" calendarPattern="yyyy-MM-dd'T'HH:mm:ss" calendarPatternKind="implicit" calendarTimeZone="UTC" choiceLengthKind="implicit" decimalSigned="yes" documentFinalTerminatorCanBeMissing="no" emptyValueDelimiterPolicy="none" encoding="US-ASCII" escapeSchemeRef="" fillByte="0" floating="no" ignoreCase="no" initiatedContent="no" initiator="" leadingSkip="0" lengthKind="explicit" lengthUnits="bytes" nilValueDelimiterPolicy="none" occursCountKind="expression" outputNewLine="%CR;%LF;" prefixIncludesPrefixLength="no" representation="text" separator="" separatorPolicy="required" separatorPosition="infix" sequenceKind="ordered" terminator="" textBidi="no" textBooleanFalseRep="false" textBooleanJustification="left" textBooleanPadCharacter="%SP;" textBooleanTrueRep="true" textCalendarJustification="left" textCalendarPadCharacter="%SP;" textNumberCheckPolicy="lax" textNumberJustification="right" textNumberPadCharacter="%SP;" textNumberRep="standard" textNumberRounding="pattern" textNumberRoundingMode="roundUp" textPadKind="none" textStandardBase="10" textStandardDecimalSeparator="." textStandardExponentCharacter="E" textStandardGroupingSeparator="," textStandardInfinityRep="Inf" textStandardNaNRep="NaN" textStandardZeroRep="" textStringJustification="left" textStringPadCharacter="%SP;" textTrimKind="padChar" textZonedSignStyle="asciiStandard" trailingSkip="0" truncateSpecifiedLengthString="no">
</dfdl:format>
</dfdl:defineFormat>
</appinfo>
</annotation>

Please help me here
Back to top
View user's profile Send private message
mqjeff
PostPosted: Tue May 02, 2017 4:07 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17190

Your date value appears to have two sets of quotes around it - ""2002"" instead of "2002".
_________________
Read, Think, Try, Repeat
Back to top
View user's profile Send private message
missing_link
PostPosted: Tue May 02, 2017 4:20 am Post subject: Reply with quote

Acolyte

Joined: 08 Jan 2004
Posts: 51

I think the error:
Error casting '2020' years '2' months '30' days to a DATE.
is fairly obvious.

You're casting to a date, but have missing date parts in your source. In my experience IIB will add the missing parts from the current system time. So you have 20 as year (which is transposed to 2020 as per the docs) and a month of 02, but IIB will add 30 if your sys date is 20th April.
Back to top
View user's profile Send private message
mindspace
PostPosted: Tue May 02, 2017 5:47 am Post subject: Reply with quote

Novice

Joined: 22 Jan 2013
Posts: 14

mqjeff wrote:
Your date value appears to have two sets of quotes around it - ""2002"" instead of "2002".


I tried with same test data, same message flow+esql+DFDL, only change is is the system time, in success case the system current date time is other than 30 April 2017 and in failure case the system time is 30 April 2017.
Back to top
View user's profile Send private message
mindspace
PostPosted: Tue May 02, 2017 5:55 am Post subject: Reply with quote

Novice

Joined: 22 Jan 2013
Posts: 14

missing_link wrote:
I think the error:
Error casting '2020' years '2' months '30' days to a DATE.
is fairly obvious.

You're casting to a date, but have missing date parts in your source. In my experience IIB will add the missing parts from the current system time. So you have 20 as year (which is transposed to 2020 as per the docs) and a month of 02, but IIB will add 30 if your sys date is 20th April.


I am sorry, but can you please explain me how it worked in other case with same test data but system local time is other 30 April 2017?
Back to top
View user's profile Send private message
missing_link
PostPosted: Tue May 02, 2017 6:52 am Post subject: Reply with quote

Acolyte

Joined: 08 Jan 2004
Posts: 51

I would also expect it fail if you set your system date 31st march.
Back to top
View user's profile Send private message
mindspace
PostPosted: Tue May 02, 2017 7:10 am Post subject: Reply with quote

Novice

Joined: 22 Jan 2013
Posts: 14

missing_link wrote:
I would also expect it fail if you set your system date 31st march.


Thank you, I understood now,so in failure case tested on 30 April IIB is interpreting 2002(yyMM) as 2020 years 02 months and 30th day which doesn't exist in February month...
Back to top
View user's profile Send private message
missing_link
PostPosted: Tue May 02, 2017 7:23 am Post subject: Reply with quote

Acolyte

Joined: 08 Jan 2004
Posts: 51

correct. you don't have a day on your source value, and your cast is going via a DATE. therefore IIB will be add the system day during the cast, which runs the risk of creating an invalid date.

i saw something similar where a date was being cast to a string via a GMTTIMESTAMP, and IIB adds the gmt timestamp during the cast.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportCAST from CHAR to DATE failing in v10.0.0.5
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.