Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

[#1]

Greetings.

My company finally was able to get JMP14 realeased so I am very excited to try out the new features. Specifically the Semiconductor Took Kit and the Calendar Box() function. So my first step is to parse the large amounts of data pulling from a data range. I finally manged to convert the 3388594698 to  an actual date.  Here is an example of the output:

sqlstart = "01/18/2018 12:00:00 AM";
sqlend = "12/12/2018 1:25:05 PM";

However our database has the timestamp formatted like 2018-10-28 16:59:03. I have tried to solve this using the Scripting Guide and posts on this website but to no avail. End of script closes the window and will open another one as Part #2 but would like the dashboard to hold everything.. But that is the next hurdle.

Any help would be greatly appreciated.

d

Close All( Data Tables, No Save );
Close All( Reports );
Clear Log();
Close All( journals, no save );
Clear Symbols();

/******************************************************************************/
/*                                                                            */
/* Brings in the list of wafers from the SQL Query [Current] 	              */
/*                                                                            */
/******************************************************************************/

testdata_HAY_QQ_LotList = Expr(
	"
	SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master  t1
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )
	WHERE t1.testdate >= cast('2018-01-01' AS date) // Currently How I select a list of lots.
		AND (t1.wafer_number NOT RLIKE '-ATL' )
		ORDER BY t1.testdate DESC
	"
);

/*******************************************************************************/
/*                                                                             */
/* Brings in the list of wafers from the SQL Query [Desired with Caledar Box]  */
/*                                                                             */
/*******************************************************************************/


testdata_HAY_QQ_LotList = Expr(
	"
		SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master  t1
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )
	WHERE (t1.testdate <= "||sqlstart||") // Varible from Date Selection
		AND (t1.testdate >= "||sqlend||") // Varible from Date Selection
		AND (t1.wafer_number NOT RLIKE '-ATL' )
		ORDER BY t1.testdate DESC
	"
);

/******************************************************************************/
/*                                                                            */
/* LotList is used in the next step of the data pull selecting the wafers     */
/*                                                                            */
/******************************************************************************/


dt_LotList = Open Database( DATABASE, testdata_HAY_QQ_LotList, "LotList" );
Summarize( LotList = By( :wafer_number ) );
Close( dt_lotlist, No Save );


/******************************************************************************/
/*                                                                            */
/* Start of Main Script [Calendar Box() to Select Wafer with Date Range]      */
/*                                                                            */
/******************************************************************************/


nw = New Window( "DATE SELECTION",
	V List Box(
		Align( Left ),
		V List Box(
			Spacer Box(
				Size( 5, 15 )
			),
			Text Box( 
				"LOT DATE RANGE",
				<< Set Font Name( "Segoe UI" ),
				<< Set Font Style( "Bold" ),
				<< Set Font Size( 12 ),
				<< Justify Text( "Center" )
			),
			Spacer Box(
				Size( 5, 20 )
			),
			H List Box(
				Panel Box( "Select Start Date",
					StartDate = Calendar Box(),
				),
				Spacer Box(),
				Panel Box( "Select End Date",
					EndDate = Calendar Box(),
				)
			),
			Panel Box( "",
				loadlot = Button Box( "LOAD LOT LIST",
					<< Set Function(
						Function(
							{self},
							sqlstart = MDYHMS( StartDate << Get Date()); // passed to database query
							sqlend = MDYHMS( EndDate << Get Date());	// passed to database query 
							Show( sqlstart );
							Show( sqlend );
							self << close window;
						)
					)
				)
			)
		)
	)
);

date_start = Date MDY( 01, 1, 2018 );

StartDate << Date( date_start );

StartDate << Show Time( 0 );

StartDate << Min Date( Date Increment( Today(), "Day", -365, "Start" ) );

date_end = Date MDY( 01, 1, 2018 );

EndDate << Date( date_end );

EndDate << Show Time( 0 );

EndDate << Min Date( Date Increment( Today(), "Day", -365, "Start" ) );

[#2]

You can use evalinsert for cleaner looking code and automatic conversion of numeric values to character strings.  Simply surround your variable with the caret character (^) like this:

testdata_HAY_QQ_LotList = evalinsert(
"SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master t1
		 LEFT OUTER JOIN tst_dataset_header t2 
			ON ( t2.run_id = t1.run_id )
	WHERE (t1.testdate <= '^sqlstart^') /* Varible from Date Selection */
	  AND (t1.testdate >= '^sqlend^') /* Varible from Date Selection */
	  AND (t1.wafer_number NOT RLIKE '-ATL' )
	ORDER BY t1.testdate DESC");