Issue
I am trying to implement a "Delete" button in an editable Appsmith Table Widget to delete data from a SQL table. However, when I click the button, I get an error message saying "Arithmetic overflow error converting expression to data type datetime." Upon checking the debug console, it seems like the data being passed is in the form of a Unix timestamp, and I am not sure how to convert it to a datetime format that SQL can recognize.
Resolution
The user has a SQL table with a datetime column called "EntryDate" and wants to implement a "Delete" button in Appsmith Table Widget that deletes the row from the SQL table when clicked. They have set the column type to "Date" and the original date format and display date format to "YYYY-MM-DD HH:mm". However, they were getting an "Arithmetic overflow error converting expression to data type datetime" error when clicking the delete button, which seemed to be caused by the data being based on a Unix timestamp or some other form of a datetime in the Appsmith debug console.
To solve the issue, used moment.js to convert the timestamp and format it correctly for the SQL query, like this:
DELETE FROM SQLTable
WHERE
EntryDate = '{{moment(TableAdvisorNote.selectedRow.EntryDate).utc().format('YYYY-MM-DD HH:mm:ss.SSS')}}'