# Unable to convert string to Date thru Metadata setting

**URL:** <https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384>\
**Category:** CloverDX Platform\
**Created:** [April 9, 2014, 12:00am UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384 "2014-04-09T00:00:00Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![devmaha](https://avatars.discourse-cdn.com/v4/letter/d/bc79bd/32.png) [@devmaha](https://forum.cloverdx.com/u/devmaha)\
**Post date:** [April 9, 2014, 12:00am UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/1 "2014-04-09T00:00:00Z")

</div>

I am using xmlExtract component to read an XML which has a date tag.

```auto
<cover>
<country>US</country>
<pack>2093780</pack>
<date>20110604</date>
</cover>

```

I am trying to read the date tag (which is a string) as a ‘date’ type by changing the _type_ in metadata to ‘date’. So initially when I had not set the right format I, understandably, got the below error

```auto
date (date) cannot be set to "20110609" - doesn't match defined format "yyyy-MM-dd"; value: '20110609'

```

But when I set the format to ‘yyyymmdd’ then I got some error like this

```auto
 date (date) cannot be set to "Sun Jan 09 00:04:00 EST 2011" - doesn't match defined format "yyyymmdd"; value: 'Sun Jan 09 00:04:00 EST 2011'

```

{This component connect to DBOutputTable but I don’t think that would matter}

So my question is what is the right (and the best) way to read a string as date and save in DB. (I don’t think I need a ‘Reformat’ component for this (I think metadata should be able to handle it)

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [April 9, 2014, 11:10am UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/2 "2014-04-09T11:10:31Z")

</div>

Hi,

Which version of CloverETL do you have, please? I tried it for myself and was able to read the XML.

However, there is a fact you should be aware of. There is a difference between “yyyyMMdd” and “yyyymmdd”. (months vs. minutes) I was able to use both these formats but both mean different date and time when used with string “20110604”.

First, please try to read your XML and save the data with UniversalDataWriter (UDR). Only after you are sure the date has the correct value and format, replace UDR with DBOutputTable.

Regards,

---

<div class="post-metadata">

**Author:** ![devmaha](https://avatars.discourse-cdn.com/v4/letter/d/bc79bd/32.png) [@devmaha](https://forum.cloverdx.com/u/devmaha)\
**Post date:** [April 9, 2014, 8:50pm UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/3 "2014-04-09T20:50:03Z")

</div>

Thank you for looking into my query  
Version: community **version 3.5.0.058**  
I have connected the xmlExtract component now to ‘Trash’ and ensured that I am using ‘yyyyMMdd’ but I am still getting error.

```auto
date (date) cannot be set to "[b]Thu Jun 09 00:00:00 EDT 2011[/b]" - doesn't match defined format "yyyyMMdd"; value: 'Thu Jun 09 00:00:00 EDT 2011'

```

My mapping is also very simple

```auto
<Mapping element="date" useParentRecord="true"
							xmlFields="../{}date"
							cloverFields="date">
					</Mapping>

```

And in the metadata, I didn’t change anything except for changing the type to ‘date’ and format to ‘yyyyMMdd’

Is there anything else that I can share to help debug this issue?

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [April 11, 2014, 1:31pm UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/4 "2014-04-11T13:31:16Z")

</div>

Some piece of information is still missing, some setting is still different between our two Designers. To speed things up, could you please send me your graph and input file?

Thanks,

---

<div class="post-metadata">

**Author:** ![devmaha](https://avatars.discourse-cdn.com/v4/letter/d/bc79bd/32.png) [@devmaha](https://forum.cloverdx.com/u/devmaha)\
**Post date:** [April 12, 2014, 1:28am UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/5 "2014-04-12T01:28:54Z")

</div>

Here is the XML

```auto
<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>20110609</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
</catalog>

```

And here is the Graph

```auto
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="AMITG" created="Fri Apr 11 18:58:31 EDT 2014" guiVersion="3.5.0" id="1397265773941" licenseType="Community" modified="Fri Apr 11 21:26:35 EDT 2014" modifiedBy="AMITG" name="Test" revision="1.3" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="recordName1" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="id" type="string"/>
<Field format="yyyyMMdd" label="publish_date" name="publish_date" type="date"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node enabled="enabled" guiName="Trash" guiX="482" guiY="162" id="TRASH0" type="TRASH"/>
<Node enabled="enabled" guiName="XMLExtract" guiX="243" guiY="162" id="XMLEXTRACT" schema="${META_DIR}\catalog.xsd" sourceUri="C:/Users/amitg/Desktop/Catalogue.xml" type="XML_EXTRACT">
<attr name="mapping"><![CDATA[<Mappings>
	<Mapping element="catalog">
		<Mapping element="book" outPort="0"
				xmlFields="{}id"
				cloverFields="id">
			<Mapping element="publish_date" useParentRecord="true"
					xmlFields="../{}publish_date"
					cloverFields="publish_date">
			</Mapping>
		</Mapping>
	</Mapping>
</Mappings>
]]></attr>
</Node>
<Edge debugMode="true" fromNode="XMLEXTRACT:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>

```

Error

```auto
------------------------------------------------------------------- Error details ---------------------------------------------------------------------
  Component [XMLExtract:XMLEXTRACT] finished with status ERROR. (Out0: 0 recs)
   publish_date (date) cannot be set to "Thu Jun 09 00:00:00 EDT 2011" - doesn't match defined format "yyyyMMdd"; value: 'Thu Jun 09 00:00:00 EDT 2011'
    Unparseable date: "Thu Jun 09 00:00:00 EDT 2011" at position 0
-------------------------------------------------------------------------------------------------------------------------------------------------------

```

{I hope it’s not a bug because that will be very disappointing}

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [April 16, 2014, 1:21pm UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/6 "2014-04-16T13:21:12Z")

</div>

To be honest, there IS a bug but just a minor one. You set the XMLExtract mapping in an unexpected way and that showed us the bug located elsewhere. However, if you set it properly, everything will work ok.

Please see the following graph. publish\_date is not populated into a parent record anymore, it is mapped directly as a child of the element book. This is the correct way in this case.

```auto
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="AMITG" created="Fri Apr 11 18:58:31 EDT 2014" guiVersion="3.5.0" id="1397265773941" licenseType="Community" modified="Wed Apr 16 15:02:48 CEST 2014" modifiedBy="user" name="Test" revision="1.19" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="recordName1" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="id" type="string"/>
<Field format="yyyyMMdd" label="publish_date" name="publish_date" type="date"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node enabled="enabled" guiName="Trash" guiX="482" guiY="162" id="TRASH0" type="TRASH"/>
<Node enabled="enabled" guiName="XMLExtract" guiX="243" guiY="162" id="XMLEXTRACT" schema="${META_DIR}\catalog.xsd" sourceUri="${DATAIN_DIR}/in.xml" type="XML_EXTRACT">
<attr name="mapping"><![CDATA[<Mappings>
	<Mapping element="catalog">
		<Mapping element="book" implicit="false" outPort="0"
				xmlFields="{}id;{}publish_date"
				cloverFields="id;publish_date">
		</Mapping>
	</Mapping>
</Mappings>
]]></attr>
</Node>
<Edge debugMode="true" fromNode="XMLEXTRACT:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>

```

By the way, I am going to report the bug to our developers to prevent any confusion in the future. Thank you for the report.

Regards,

---

<div class="post-metadata">

**Author:** ![devmaha](https://avatars.discourse-cdn.com/v4/letter/d/bc79bd/32.png) [@devmaha](https://forum.cloverdx.com/u/devmaha)\
**Post date:** [April 16, 2014, 7:21pm UTC](https://forum.cloverdx.com/t/unable-to-convert-string-to-date-thru-metadata-setting/1384/7 "2014-04-16T19:21:46Z")

</div>

Well I am glad I could help in discovery of a bug. Plus I learnt another way of mapping which works for me.  
Thanks for helping