topic Re: SQL query script problem with date format in Discussions

SQL query script problem with date format

Teissduc16 — Fri, 09 Jun 2023 18:09:25 GMT

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,

Re: SQL query script problem with date format

ih — Mon, 24 Jan 2022 12:51:05 GMT

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);

Re: SQL query script problem with date format

jthi — Mon, 24 Jan 2022 12:51:32 GMT

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' ";

Re: SQL query script problem with date format

Teissduc16 — Mon, 24 Jan 2022 17:39:30 GMT

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

Regards