CloverETL is now CloverDX - Learn Why

Str2Date problem

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Michaelj
Posts: 4
Joined: Tue Feb 06, 2018 10:32 pm

Str2Date problem

Postby Michaelj » Wed Feb 28, 2018 5:34 pm

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

bartonv
Posts: 87
Joined: Wed May 03, 2017 12:10 pm

Re: Str2Date problem

Postby bartonv » Mon Mar 05, 2018 4:16 pm

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 component, the isDate function). If this does not help you overcome the error, please get back to us with these additional items of information:
  1. The version of CloverETL Designer
  2. Your graph (or at least the part of the graph where sample data is read from a file and handled by Reformat)
  3. Make sure to attach also the sample input data, and metadata in case it is externalized (not part of the graph)
Regards,
Attachments
str2date.grf
(11.19 KiB) Downloaded 45 times
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

the_goat
Posts: 21
Joined: Fri Dec 01, 2017 6:16 am

Re: Str2Date problem

Postby the_goat » Mon Mar 05, 2018 4:33 pm

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.