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, the user used a query to convert the Appsmith timestamp to a SQL datetime format before deleting the row from the table. The solution involved using moment.js to convert the timestamp and format it correctly for SQL, like this:
DELETE FROM SQLTable
WHERE
EntryDate = '{{moment(TableAdvisorNote.selectedRow.EntryDate).utc().format('YYYY-MM-DD HH:mm:ss.SSS')}}'
This query takes the Appsmith timestamp in TableAdvisorNote.selectedRow.EntryDate, uses moment.js to convert it to a UTC datetime in the format 'YYYY-MM-DD HH:mm:ss.SSS', and uses that formatted datetime to delete the correct row from the SQL table.