GoAnywhere Forum

Posted: **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>

Posted: **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>

GoAnywhere Forum

Date Error

Date Error

Re: Date Error