Str2Date problem

michaelj

• February 28, 2018 00:00

• Answered

Follow

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

• 

  Vladimir Barton

  • March 05, 2018 15:16

  0

  Comment actions Permalink

  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:
  

  • 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
• 

  the_goat

  • March 05, 2018 15:33

  0

  Comment actions Permalink

  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.

Please sign in to leave a comment.