topic Re: Use date variable in Open Database JSL command in Discussions

Use date variable in Open Database JSL command

terapin — Fri, 09 Jun 2023 19:54:58 GMT

I have a JSL script that opens a SQL database and then processes the data for various purposes. My challenge is passing a date variable to the Open Database command to further refine the automation of the process. I've tried a whole host of options for trying to do this, but nothing has worked so far. Does anyone have any suggestions for how I can replace the Date_Time value listed in the Open Database command with the variable datetime shown below?

datetime = "2021-08-01";

// Program opens diagnostic SQL data table for all instruments
dt.db_diag = Open Database(
"DSN=AQM;Description=AQM;UID=sa;PWD=LKJDFDF;Trusted_Connection=No;APP=JMP;WSID=TESTPC;DATABASE=EnvidasConfig;",
"SELECT * FROM [dbo].MonitorDiagnosticsData WHERE Date_Time >= '2021-08-18'"
);

Re: Use date variable in Open Database JSL command

txnelson — Wed, 18 Aug 2021 22:49:23 GMT

See if this will do what you need

datetime = "2021-08-01";

// Program opens diagnostic SQL data table for all instruments


Eval(
	Parse(
		"dt.db_diag = Open Database(
\!"DSN=AQM;Description=AQM;UID=sa;PWD=LKJDFDF;Trusted_Connection=No;APP=JMP;WSID=TESTPC;DATABASE=EnvidasConfig;\!",
\!"SELECT * FROM [dbo].MonitorDiagnosticsData WHERE Date_Time >= '"
		 || datetime || "'\!"
);"
	)
);

Re: Use date variable in Open Database JSL command

terapin — Thu, 19 Aug 2021 14:33:49 GMT

That works perfectly,

I also now see where I was putting the ' in the wrong location during my testing. Thanks for the quick and helpful reply.