Support Questions
Find answers, ask questions, and share your expertise

Implicit conversion error in PutSQL date field

Explorer

I am using apache nifi to read csv file data and store the result in an Oracle Database.

The dataflow i used in nifi is as follows:

 

GetFile

ConvertRecord ( here just to convert csv to Json)

ConvertJsonToSQL (just to convert Json to SQL INSERT query)

UpdateAttribute (which formats CreateDate using the following format ${sql.args.5.value:toDate("dd-MMM-yy"):format("dd-MMM-yy")}

PutSQL (to execute the insert statement created from previous flow)

 

Step 5 is where i encounter the error below and i've tried for a couple of resources online and it didn't help. I need some advice on how to properly configure and insert a data on a date column.

 

ERROR LOG

 

 due to java.sql.SQLDataException: The value of the sql.args.5.value is '20-May-21', which cannot be converted to a timestamp; routing to failure: java.sql.SQLDataException: The value of the sql.args.5.value is '20-May-21', which cannot be converted to a timestamp

DB Schema

CreateDate DATE

 

UpdateAttribute Outgoing 

Modified Data

20-May-21

Previous data

20-MAY-21

 

sql.args.39.type

93

sql.args.39.value

20-May-21

 

I tried removing update attribute and directly change the sql statement property using insert statement. That also didn't work. Encountered the same exception mentioned above. Please suggest with the solution

 

Insert into table(CreateDate,...) 

Values ($CreateDate:toDate("dd-MMM-yy"):format("dd-MMM-yy")

1 REPLY 1