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")
