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

Mveluru

Posts: 5
Joined: Mon Jan 28, 2013 6:05 pm
Location: Austin

Post by 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 462 times

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by 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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts Page 1 of 1