| Author |
Message
|
| mindspace |
 Posted: Mon May 01, 2017 2:09 pm Post subject: CAST from CHAR to DATE failing in v10.0.0.5 |
 |
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
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 |
|
 |
| mqjeff |
| Posted: Tue May 02, 2017 4:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Your date value appears to have two sets of quotes around it - ""2002"" instead of "2002".
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| missing_link |
| Posted: Tue May 02, 2017 4:20 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
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 |
|
|
| mindspace |
| Posted: Tue May 02, 2017 5:47 am Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| 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 |
|
|
| mindspace |
| Posted: Tue May 02, 2017 5:55 am Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| 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 |
|
|
| missing_link |
| Posted: Tue May 02, 2017 6:52 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
| I would also expect it fail if you set your system date 31st march. |
|
| Back to top |
|
|
| mindspace |
| Posted: Tue May 02, 2017 7:10 am Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| 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 |
|
|
| missing_link |
| Posted: Tue May 02, 2017 7:23 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
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 |
|
|
|
|
