Interactive date selection and filtering by SQL

[#1]

I like to use JMP interative to get input start and end time and then use SQL to filter the data table. The parameter dateb and datee if defined as for instance '2017-10-10', '2017-11-11' works fine but when use assigned varaible dateb and datee, I don't get the correct filtering (empty tables).

I appreciate any help you can provide. Thanks.

------------------------------------

defuplim = Today();

deflowlim = Today() - In Days( 730 );

ww = New Window( "Pick Your Dates in yyyy-mm-dd format",

<<Modal,

<<return result,

V List Box(

Text Box( "Start Date:" ),

str1 = Number Edit Box(

deflowlim,

10,

<<SetFunction(

Function( {that},

lowlim = str1 << get

)

)

),

Text Box( "End Date:" ),

str2 = Number Edit Box(

defuplim,

10,

<<SetFunction(

Function( {this},

uplim = str2 << get

),

)

),

str1 << set format( Format( "yyyy-mm-dd" ) ),

str2 << set format( Format( "yyyy-mm-dd" ) )

),

H List Box( Button Box( "OK" ), Button Box( "Cancel" ) ),

);

dateb = As date(ww["str1"]);

datee = As date(ww["str2"]);

dateb = format(dateb,"yyyy-mm-dd");

datee = format(datee,"yyyy-mm-dd");

dt1 = Open Database(

"DSN=XXX;UID=root;PWD=xxxxx;",

"Select * from xyz where date(starttime) between 'dateb' and 'datee'","TMP"

);

[#2]

You need to convert your string dates back into dates for the database to understand them.  If this is Oracle you need a statement like:

sql_statement = evalinsert(
"Select * 
   from xyz 
  where date(starttime) between to_date('^dateb^', 'YYYY-MM-DD')
                            and to_date('^datee^', 'YYYY-MM-DD') ");

dt1 = Open Database("DSN=XXX;UID=root;PWD=xxxxx;", sql_statement);

[#3 · Accepted solution]

See if this modification will work

defuplim = Today();
deflowlim = Today() - In Days( 730 );

ww = New Window( "Pick Your Dates in yyyy-mm-dd format", 
	<<Modal, 
	<<return result, 
	V List Box(
		Text Box( "Start Date:" ), 
		str1 = Number Edit Box(
			deflowlim, 
			10, 
			<<SetFunction(
				Function( {that}, 
					lowlim = str1 << get
				)
			)
		), 
		Text Box( "End Date:" ), 
		str2 = Number Edit Box(
			defuplim, 
			10, 
			<<SetFunction(
				Function( {this}, 
					uplim = str2 << get
				), 
			)
		), 
		str1 << set format( Format( "yyyy-mm-dd" ) ), 
		str2 << set format( Format( "yyyy-mm-dd" ) )
	), 
	H List Box( Button Box( "OK" ), Button Box( "Cancel" ) ),        
);

dateb = As Date( ww["str1"] );
datee = As Date( ww["str2"] );

dateb = Format( dateb, "yyyy-mm-dd" );
datee = Format( datee, "yyyy-mm-dd" );

Eval(
	Parse(
		"dt1 = Open Database(
	\!"DSN=XXX;UID=root;PWD=xxxxx;\!", 
	\!"Select * from xyz where date(starttime) between '"
		 || dateb || "' and '" || datee || "',\!"TMP\!");"
	)
);

[#4]

Thanks so much Jim. It worked perfectley.