cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
752
Views
0
Helpful
1
Replies

CVP Studio DateTime SQL Select

Amer rajai Sha'er
Rising star
Rising star

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

 

1 Accepted Solution

Accepted Solutions

janinegraves
Spotlight
Spotlight

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}'

View solution in original post

1 Reply 1

janinegraves
Spotlight
Spotlight

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}'

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: