Solved

Convert String to Datetime Error

  • 12 November 2021
  • 6 replies
  • 1355 views

Hello,

I am using a Field Type Converter to convert string to datetime with date format (yyyy-MM-dd'T'HH:mm:ss.SSS'Z' -  2021-08-18T08:20:24.136000Z).  However, the same field has dates with format 2021-08-18T08:20:24.00Z, which it gives me error CONVERTER_00 - Failed to convert field '/createdate' of type 'STRING' with value '2021-08-19T14:01:32Z' to type 'DATETIME'

 

Any recommendations on how to handle two different date formats for the same field?

 

icon

Best answer by Dimas Cabré i Chacón 15 November 2021, 22:01

View original

6 replies

Userlevel 2
Badge

Since Java 8, you can use optional formatter options to parse and output dates. Use the following format instead of the one you show: 

[yyyy-MM-dd'T'HH:mm:ss.SSSSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SS'Z'][yyyy-MM-dd'T'HH:mm:ss.S'Z'][yyyy-MM-dd'T'HH:mm:ss'Z']

Sorry for the as I am a newbie in streamset.

So, under he Conversions tab in the Field Type Converter, I would create 2 “section” to convert /createdate.  One section would have date format yyyy-MM-dd'T'HH:mm:ss.SSS'Z', and another that would have yyyy-MM-dd'T'HH:mm:ss'Z'

 

Userlevel 2
Badge

No. Just use “Other Date Format” and the specify as format (single long line):

[yyyy-MM-dd'T'HH:mm:ss.SSSSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SSS'Z'][yyyy-MM-dd'T'HH:mm:ss.SS'Z'][yyyy-MM-dd'T'HH:mm:ss.S'Z'][yyyy-MM-dd'T'HH:mm:ss'Z']

This should work with one sinlgle section.

@Dimas Cabré i Chacón it is still failing.

 

 

Userlevel 2
Badge

@mtaka Sorry. You are right. My suggestion would work if we used DatetTimeFormatter to make the datetime conversion, but currently we use SimpleDateFormat, which does not support optional formatting. Currently there is no direct method to accomplish this. A reasonable approach is stagging the conversion in two separate phases: normalization and conversion. You first normalize fields using processor “Field Replacer”. Then, having assured all fields have the same format, you convert them to datetime using “Field Type Converter” processor. This is one replacement expression that would do the trick:

${str:length(record:value('/datetime')) > 20 ? record:value('/datetime') : str:concat(str:substring(record:value('/datetime'), 0, 19), '.000000Z')}

You will need to work the finer details if more input formats can appear.

I can send you a sample pipeline if you face some trouble configuring this.

Userlevel 3
Badge

@mtaka you may refer to 

This may help. If you like, don't forget to give a thumbs up.

Reply