CVP Studio DateTime SQL Select

[Amer rajai Sha'er]

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

[janinegraves · Accepted solution]

1. I assume you just forgot the WHERE in your post, otherwise it's not valid.

2. To see the actual query being sent to SQL, you should remove the line breaks and copy that query into the Add To Log (in the General tab). Then you'll be able to see what's being sent to SQL - that may help you solve the problem.
3.  My guess is that you probably have to embed the LocalVar reference inside apostrophes  '{LocalVar.DateTime}' and include the keyword WHERE:
select *  from XXX
WHERE DateTime >= '{LocalVar.DateTime}'