I have to delete all the records from H2 Database by matching completed_date column with current time stamp where difference in days are greater than 1 (or an number of days).
There is one problem with the schema of database that completed_date is stored as String in the given format 11-Jan-2018 15:35:30 PM i.e 'dd-MMM-yyyy HH:mm:ss aaa' format.
There are some more parameters where status should be matched.
I am getting this Exception
Cannot parse "TIMESTAMP" constant "28-12-2017 03:12:47"; SQL statement:
The query I have written is as below.
delete from TABLE_NAME
where
status = 'status1'
OR status = 'status2'
OR status = 'status3'
AND
TIMESTAMPDIFF(DAY,TO_CHAR(PARSEDATETIME(completed_date,'dd-MMM-yyyy HH:mm:ss
aaa'),'dd-MM-yyyy HH:mm:ss'),CURRENT_TIMESTAMP()) >= 1;
In above query I have found that TIMESTAMPDIFF does not work with date format dd-MMM-yyyy HH:mm:ss aaa so first I have tried to parse it in 'dd-MM-yyyy HH:mm:ss' format and if use this below query it gives me proper result
SELECT TO_CHAR(PARSEDATETIME('2017-OCT-2017 15:49:47 PM','dd-MMM-yyyy
HH:mm:ss aaa')
,'dd-MM-yyyy HH:mm:ss')
Just giving some more information that i am coding Workfusion which is RPA tool which internally uses h2-database so if anyone from workfusion here they can also help me.
timestampdiff() takes two timestamps as the input, however you are passing a string and a timestamp. So the string gets converted back to a timestamp using some default format.
You should be using:
TIMESTAMPDIFF(DAY,PARSEDATETIME(completed_date,'dd-MMM-yyyy HH:mm:ss aaa'), CURRENT_TIMESTAMP())
(assuming that parsedatetime() can successfully parse the string)