Solved

Convert String to Datetime Error

2 years ago
12 November 2021
6 replies
1296 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?

icon

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

View original

6 replies

Userlevel 2

Dimas Cabré i Chacón
StreamSets Employee
37 replies
2 years ago
12 November 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
2 years ago
12 November 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'

Userlevel 2

Dimas Cabré i Chacón
StreamSets Employee
37 replies
2 years ago
13 November 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.

mtaka
Author
Fan
2 replies
2 years ago
15 November 2021

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

Userlevel 2

Dimas Cabré i Chacón
StreamSets Employee
37 replies
2 years ago
15 November 2021
Answer

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