Solved

Convert String to Datetime Error

3 years ago
November 12, 2021
6 replies
1525 views

mtaka
Fan
2 replies

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?

Best answer by Dimas Cabré i Chacón

@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.

View original

Did this topic help you find an answer to your question?

Dimas Cabré i Chacón
StreamSets Employee
38 replies
3 years ago
November 12, 2021

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']

mtaka
Author
Fan
2 replies
3 years ago
November 12, 2021

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'

Dimas Cabré i Chacón
StreamSets Employee
38 replies
3 years ago
November 13, 2021

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.