SQL query script problem with date format

[#1]

Hello, 

I'm a biginner on JSL. I'm mixing a script with a SQL Query. My query conditionned by two date. But when I run it the assignement of my date deosn't work. The fild with the date is _TIMESTAMP and the format of the date is yyyy-mm-dd hh:mm:ss. When I run it with the dates inside the query of course it works. I need this script because I have to do the same thing for numerous tables. 

Here is the script : 

RunScript= Expr( 
  sql_statement=evalinsert("SELECT * FROM table_SQL WHERE  _TIMESTAMP >= '^start^' AND  _TIMESTAMP < '^end^' ");
  start=(2021-12-15 00:00:00); 
  end=(2021-12-18 00:00:00); 
  Open Database("Driver=§§§§§§§§§§§§§§§§§§§§§§§§", 
  sql_statement,"table_name_1"));

I have blinded the sql connexion parameters. 

Kind regards, 

[#2]

Welcome to the community @Teissduc16,

A couple of small things that might help:

• You probably want to define start and end before you use them in the eval insert() line.
• To make sure the date shows up in a format SQL understands, you probably want to format dates as strings before putting them in the sql statement

Here is an example:

start=("2021-12-15 00:00:00"); 
end=format(date dmy(1,1,2021), "yyyy-mm-dd hh:mm:ss");
sql_statement=evalinsert("SELECT * FROM table_SQL WHERE  _TIMESTAMP >= '^start^' AND  _TIMESTAMP < '^end^' ");
show(sql_statement);

[#3 · Accepted solution]

sql_statement will error if you don't have start and end defined before the eval insert statement, also change start and end to strings

Names Default To Here(1);

//this will error because it is before start and end variable definition
//sql_statement=evalinsert("SELECT * FROM table_SQL WHERE  _TIMESTAMP >= '^start^' AND  _TIMESTAMP < '^end^' ");

start = "2021-12-15 00:00:00"; 
end = "2021-12-18 00:00:00"; 
sql_statement=evalinsert("SELECT * FROM table_SQL WHERE  _TIMESTAMP >= '^start^' AND  _TIMESTAMP < '^end^' ");
show(sql_statement);
//sql_statement = "SELECT * FROM table_SQL WHERE _TIMESTAMP >= '2021-12-15 00:00:00' AND _TIMESTAMP < '2021-12-18 00:00:00' ";

[#4]

Thank you very much for your answer. It returns well what I want. 

Regards