Hello,
I am trying to lookup a data based on datetime from SQL:
select *
from XXX
DateTime >= '2021-11-08 00:00:00'
In case the DateTime is fixed as above, it is working normally
But if i try to generate the DateTime based on the call start, it gives error:
Here is what i tried to do:
On CVP Studio, i added a variable called Year, Day and Month
The year is based on {GeneralDateTime.Year.CALL_START}
If the day is between 1 and 9 , the Day variable is : '0'+{GeneralDateTime.DayOfMonth.CALL_START} , otherwise it is just the dayof month
If the month is between 1 and 9, the month variable is : '0'+{GeneralDateTime.Month.CALL_START} othwerwise it is just the Month
After i generate the above three, i created a new variable called DateTime and the formula is :
{LocalVar.Year}+"-"+{LocalVar.Month}+"-"+{LocalVar.Day} + " 00:00:00"
In the activity log, i can see the value as :
11/08/2021 15:46:47.066,DateTime,custom,DateTime,2021-11-08 00:00:00
As for the DB element, it is like this :
select *
from XXX
DateTime >= {LocalVar.DateTime}
The element failed and it is throwing SQL error :
The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. Incorrect syntax near '00'. The root cause was: java.sql.SQLException: Incorrect syntax near '00'.
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
CVP is 12.5
