# Str2Date problem

**URL:** <https://forum.cloverdx.com/t/str2date-problem/1943>\
**Category:** CloverDX Platform\
**Created:** [February 28, 2018, 12:00am UTC](https://forum.cloverdx.com/t/str2date-problem/1943 "2018-02-28T00:00:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![michaelj](https://avatars.discourse-cdn.com/v4/letter/m/7c8e57/32.png) [@michaelj](https://forum.cloverdx.com/u/michaelj)\
**Post date:** [February 28, 2018, 12:00am UTC](https://forum.cloverdx.com/t/str2date-problem/1943/1 "2018-02-28T00:00:00Z")

</div>

I am getting an error when I try to convert a string into a date:

> Effective\_Date (string) cannot be set to “2017-01-01” - doesn’t match defined format “yyyy-MM-dd” in record 1, field 7 (“Effective\_Date”), metadata “WayneimportFixdate”; value: ‘2017-01-01’

This is the code that is generating the error from a reformat component:

> str2date($in.0.Effective\_Date, “yyyy-MM-dd”)

The reason i’m even doing this is because the file I am working with has blank dates come through as “0000-00-00” so I first am trying to import as text and then null out those bad dates and then convert to a date.

Any ideas would be helpful.

thanks,  
Mike

---

<div class="post-metadata">

**Author:** ![Vladimir\_Barton](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/vladimir_barton/32/167_2.png) [@Vladimir\_Barton](https://forum.cloverdx.com/u/Vladimir_Barton)\
**Post date:** [March 5, 2018, 3:16pm UTC](https://forum.cloverdx.com/t/str2date-problem/1943/2 "2018-03-05T15:16:47Z")

</div>

Hello Mike,  
I have tried to manufacture a situation when I would get the same error message but I did not manage to do that. In my case, the code in the Reformat component works as expected and no error is thrown by the system. I am attaching a graph showing my data transformation along with a demonstration usage of a few useful functions in regards to your use case (the [Validator](http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/validator.html?resultof=%22%76%61%6c%69%64%61%74%6f%72%22%20) component, the [isDate](http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/string-functions-ctl2.html#ctl2-isDate) function). If this does not help you overcome the error, please get back to us with these additional items of information:

- The version of CloverETL Designer

- Your graph (or at least the part of the graph where sample data is read from a file and handled by Reformat)

- Make sure to attach also the sample input data, and metadata in case it is externalized (not part of the graph)

Regards,

- [str2date.grf](https://cdn.cloverdx.com/forum/18401%5Ff6149e66de27a3ad352072d67f18fe43/str2date.grf)

---

<div class="post-metadata">

**Author:** ![the\_goat](https://avatars.discourse-cdn.com/v4/letter/t/f475e1/32.png) [@the\_goat](https://forum.cloverdx.com/u/the_goat)\
**Post date:** [March 5, 2018, 3:33pm UTC](https://forum.cloverdx.com/t/str2date-problem/1943/3 "2018-03-05T15:33:38Z")

</div>

Well, not sure where you’re getting this error. But on both cases it is metadata configuration:  
[\*] In case your reader is failing, make sure your _Effective\_Date_ does NOT have _Format_ property set  
[\*] In case your reformat is failing, make sure your _Effective\_Date_ is in fact “date” datatype

In any case. I’d recommend using (Advanced) property _Null value_ instead. If you set it to “0000-00-00” (without quotes), this value gets translated into _null_ value and therefore no conversion is necessary.
