value cannot be converted to a timestamp

[#1]

I have a mysql destination table with column type datetime.

my nifi flow is like convertJsontoSQL -> putsql (into destination table) The action is delete for putsql.

after conversion of jsontosql i am getting a value '2023-03-28 15:54:28' which is a valid timestamp.

but the flow is failing with below error:

duetoThevalue of the sqlargs. 14.value is '2023-03-28 15:54:28',

which cannot be converted to a timestamp; routing to failure: java.sql.SQLDataException:

The value of the sql.args. 14.value is '2023-03-28 15:54:28',

which cannot be converted to a timestamp

- Caused by: java. text. ParseException: Unparseable date: "2023-03-28 15:54:28"

I can able to use the same "2023-03-28 15:54:28" for manually inserting into a sample datetime column.

and can able to delete a record with where clause as this value.

I could not understand why Nifi is still not able to convert it to timestamp.

Any suggestions are appreciated.

[#2]

Is this "convertJsontoSQL" something custom you've built?

[#3]

@joseomjr, I'm pretty sure it's a component that has been around from some time: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.6.0/org.apache...

[#4]

Hi @SandyClouds ,

I ran into this issue before and after some research I found that when you do the ConvertJsonToSQL nifi assigns timestamp data type (value = 93 in the sql.args.[n].type attribute ). When the PutSQL runs the generated sql statement it will parse the value according to the assigned type and format it accordingly. However for timestamp it expects it to be in the format of  "yyyy-MM-dd HH:mm:ss.SSS" so if you are missing the milliseconds in the original datetime value it will fail with the specified error message. To resolve the issue make sure to assign 000 milliseconds to your datetime value before running the PUTSQL processor. You can do that in the source Json itself before the conversion to SQL or after conversion to SQL using UpdateAttribute, by using the later option you have to know which sql.args.[n].value will have the datetime and do expression language to reformat.

If that helps please accept solution.

Thanks