Date Error

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.

Date Error

Postby Mveluru » Mon Feb 11, 2013 7:04 pm

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
-----------------------------------------------------------------------------------------------------------------------------------------
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="&apos;&apos;" 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="&apos;&apos;" trim="both" />
            <column index="2" value="catalog/book/author" name="author" type="VARCHAR" nullSubstitute="&apos;&apos;" 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="&apos;&apos;" trim="both" />
            <column index="6" value="catalog/book/publish_date" name="publish_date" type="VARCHAR" pattern="yyyyMMdd" locale="en_US" nullSubstitute="&apos;&apos;" trim="both" />
            <column index="7" value="catalog/book/description" name="description" type="VARCHAR" nullSubstitute="&apos;&apos;" trim="both" />
         </rowset>
      </xmlRead>

      <forEachLoop itemsVariable="${catalogData}" currentItemVariable="ThisRec" logLevel="debug" disabled="false">

         <setVariable name="pdate" value="${FormatTimestamp(ThisRec[6],&apos;MM/dd/yyyy&apos;)}" 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 (&apos;${ThisRec[1]}&apos;, &apos;${ThisRec[2]}&apos;, &apos;${ThisRec[3]}&apos;, &apos;${ThisRec[4]}&apos;, ${ThisRec[5]}, &apos;${pdate}&apos;, &apos;${ThisRec[7]}&apos;)</statement>
            </query>
         </sql>

      </forEachLoop>
   </module>

</project>
Attachments
1359557334610.log
log file
(18.05 KiB) Downloaded 115 times
Mveluru
 
Posts: 5
Joined: Mon Jan 28, 2013 6:05 pm
Location: Austin

Re: Date Error

Postby Support_Rick » Wed Feb 13, 2013 4:08 pm

Murali,

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
<column index="6" value="catalog/book/publish_date" name="publish_date" type="VARCHAR" pattern="yyyyMMdd" locale="en_US" nullSubstitute="&apos;&apos;" trim="both" />

to this:
Code: Select all
<column index="6" value="catalog/book/publish_date" name="publish_date" type="DATE" pattern="yyyyMMdd" locale="en_US" nullSubstitute="&apos;&apos;" trim="both" />


and then, just insert the value into your DB2 Database as:

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
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 225
Joined: Tue Jul 17, 2012 2:12 pm


Return to Community Forum

Who is online

Users browsing this forum: No registered users and 0 guests

cron