topic issue to insert date variable into customsql in Discussions

issue to insert date variable into customsql

dadawasozo — Tue, 20 Jun 2023 13:55:01 GMT

Hi,

I want to set the start and end datetime as variable then insert into customsql. in method one below, JMP Alert of converting issue. When I simply hard coded the start and end date as variable, it run without issue. I check both the start and end date type in both method, both are string(). Event the custom sql after substitute into are identical. I cannot find out why the method one not working on date variable. Can someone help?? I really dont want to have to manually change the date everyday. How can I make method 1 works?

method 1 & 2

//method 1 to define start and end  date (not working)
sdate= format(today()-inhours(24), "m/d/y");  //method 1
tstart = sdate || " 19:13:01 PM";// method 1

edate= format(today(), "m/d/y"); //method 1
tend = edate || " 12:13:00 AM"; //method 1

//method 2 to define start and end date (working)
tend= "06/19/2023 19:13:01 PM"; //method 2
tstart= "06/19/2023 12:13:00 AM";  //method2

Assign(sql_str,  "SELECT t1.[test], CAST(t2.[testplan ] AS INTEGER), bla bla bla
WHERE  (  (  ( t1.[end_time] >= &tstart  )  AND  ( t1.[end_time] <= &tend  )  )  ) ;"
);
substitute Into(sql_str, "&tstart", "'"||tstart ||"'");
substitute Into(sql_str, "&tend", "'"||tend ||"'");
dt = New SQL Query (Version(130), connection("mydatabaseinfor"), QueryName("mysqlname" ), CustomSQL(sql_str)) << Run Foreground;

Re: issue to insert date variable into customsql

jthi — Tue, 20 Jun 2023 15:55:15 GMT

Does your method1 provide you with correct strings? It could be that the date is formatted incorrectly.

Names Default To Here(1);

sdate = today()-inhours(24);
m = Month(sdate);
y = Year(sdate);
d = Day(sdate);
sdate = Eval Insert("^m^/^d^/^y^");
tstart = sdate || " 19:13:01 PM";

Re: issue to insert date variable into customsql

dadawasozo — Tue, 20 Jun 2023 17:10:23 GMT

the method1 give the same identical string as method2 when I do show().

I tried your method, and still getting same error.

Re: issue to insert date variable into customsql

jthi — Tue, 20 Jun 2023 18:38:00 GMT

Checking your script again, you have tstart and tend different on method1 and method2. Maybe it is causing issues during the query?

Re: issue to insert date variable into customsql

dadawasozo — Tue, 20 Jun 2023 18:48:17 GMT

dont think so, I tried same as method 2 but still have same issue.
could it be bug in jmp pro 16.2?