How to use evalinsert on SQL Query?

[#1]

Hi all,

I am trying to query using a list and a variable. However, I cannot figure out how to query the dateNow variable. I keep on getting this error:

Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 4: and test_date_time between 03/12/2024 00:00:00 AM and 03/12/...

Here is my sample script:

namecol= {"James", "John", "Anna", "Rose")
dateNow = "03/12/2024"
eval(parse(evalinsert("\[ 

	dt_qst = Try ( Open Database( "DSN=dbs;UID=uname;PWD=pw;APP=JMP;DATABASE=db;", 
"select name, age, address, test_date_time
from  db
where name in (^namecol^)
and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'", 
"rawdata") )
	
]\")));;

[#2]

There are many small typos in your script which prevent it from working. Outside of that you need to use Concat Items to go from list to a string. Also to make it a bit easier to debug, build your query string outside of Open Database and then use that new string inside it.

Names Default To Here(1);

namecol= {"James", "John", "Anna", "Rose"};
dateNow = "03/12/2024";

sql_template = "\[
select
	name, 
	age, 
	address, 
	test_date_time
from db
where
	name in ('^namestr^')
	and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'
]\";


namestr = Concat Items(namecol, "', '");

sql_str = Eval Insert(sql_template);

I also think this has been basically answered already twice

How to skip item on JSL list in SQL query if it does not exist? and How to query in SQL using list from JSL 

[#3]

Yes, but I don't understand why I kept on getting this error

Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 10: and test_date_time between 03/12/2024 00:00:00 AM and 03/12...

[#4]

Maybe your dates aren't correctly formatted? Most likely you are missing ' around the dates