Date Error
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts
Page 1 of 1
Hello All
I'm the reading xml data using readxml task . xml contain the date yyyyMMdd
data element looks as below(also attached xml format data)
<publish_date>20001001</publish_date>
while reading the above data I'm reading like varchar of format yyyyMMdd
while inserting into database using for each loop I used function like
${FormatTimestamp(ThisRec[6],'MM/dd/yyyy')}
I alway see date always coverted to 12/31/1969 . Is there any correct function I can use to convert the date into format MM/dd/yyyy
for your referral I have attached my project.xml
-----------------------------------------------------------------------------------------------------------------------------------------
I'm the reading xml data using readxml task . xml contain the date yyyyMMdd
data element looks as below(also attached xml format data)
<publish_date>20001001</publish_date>
while reading the above data I'm reading like varchar of format yyyyMMdd
while inserting into database using for each loop I used function like
${FormatTimestamp(ThisRec[6],'MM/dd/yyyy')}
I alway see date always coverted to 12/31/1969 . Is there any correct function I can use to convert the date into format MM/dd/yyyy
for your referral I have attached my project.xml
-----------------------------------------------------------------------------------------------------------------------------------------
Code: Select all
<project name="ReadXML and Insert into DB" mainModule="Main" version="2.0" logLevel="debug">
<module name="Main">
<xmlRead file="C:\BatchOutput\processzone\GoAnywhere\catalog.xml" defaultTrim="both" defaultNullSubstitute="''" skipInvalidRecords="true" defaultDateFormat="yyyymmdd" version="1.0" logLevel="debug">
<rowset name="catalogData" nestedSharedValues="false">
<column index="1" value="catalog/book/id" name="bookID" type="VARCHAR" nullSubstitute="''" trim="both" />
<column index="2" value="catalog/book/author" name="author" type="VARCHAR" nullSubstitute="''" trim="both" />
<column index="3" value="catalog/book/title" name="title" type="VARCHAR" />
<column index="4" value="catalog/book/genre" name="genre" type="VARCHAR" />
<column index="5" value="catalog/book/price" name="price" type="DECIMAL" nullSubstitute="''" trim="both" />
<column index="6" value="catalog/book/publish_date" name="publish_date" type="VARCHAR" pattern="yyyyMMdd" locale="en_US" nullSubstitute="''" trim="both" />
<column index="7" value="catalog/book/description" name="description" type="VARCHAR" nullSubstitute="''" trim="both" />
</rowset>
</xmlRead>
<forEachLoop itemsVariable="${catalogData}" currentItemVariable="ThisRec" logLevel="debug" disabled="false">
<setVariable name="pdate" value="${FormatTimestamp(ThisRec[6],'MM/dd/yyyy')}" version="2.0" logLevel="debug" disabled="false" onError="continue" />
<print label="My PRINT" version="1.0" logLevel="debug" disabled="false" onError="abort">
<![CDATA[---------------${pdate}
---------------${ThisRec[6]}]]>
</print>
<sql resourceId="LocalDBConnect" autoCommit="false" version="1.0" logLevel="debug" disabled="false">
<query whenNoDataFound="error">
<statement>INSERT INTO MT2BATCH.BOOKSCATALOG
(ID, AUTHOR, TITLE, GENRE, PRICE, PUBLISH_DATE, DESCRIPTION)
VALUES ('${ThisRec[1]}', '${ThisRec[2]}', '${ThisRec[3]}', '${ThisRec[4]}', ${ThisRec[5]}, '${pdate}', '${ThisRec[7]}')</statement>
</query>
</sql>
</forEachLoop>
</module>
</project>
Attachments
1359557334610.log
log file
(18.05 KiB) Downloaded 766 times
(18.05 KiB) Downloaded 766 times
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Murali,
In this case, the DB will usually accept the Date Field by ISO standards (yyyy-mm-dd)
I would change your value for:
In this case, the DB will usually accept the Date Field by ISO standards (yyyy-mm-dd)
I would change your value for:
Code: Select all
to this:
<column index="6" value="catalog/book/publish_date" name="publish_date" type="VARCHAR" pattern="yyyyMMdd" locale="en_US" nullSubstitute="''" trim="both" />
Code: Select all
and then, just insert the value into your DB2 Database as:<column index="6" value="catalog/book/publish_date" name="publish_date" type="DATE" pattern="yyyyMMdd" locale="en_US" nullSubstitute="''" trim="both" />
Code: Select all
<statement>
INSERT INTO WRELLIOTT.BOOKSCATALOG(ID, AUTHOR, TITLE, GENRE, PRICE, PUBLISH_DATE, DESCRIPTION)
VALUES ('${ThisRec[1]}', '${ThisRec[2]}', '${ThisRec[3]}', '${ThisRec[4]}', ${ThisRec[5]}, '${ThisRec[6]}', '${ThisRec[7]}')
</statement>
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts
Page 1 of 1