Passing Date value to Oracle SQL

Question

I have a frustrating problem. I want to write a script to use a JMP date code, to query an Oracle database.

For instance:

StDt = today() - in weeks(4);

.

If I manually do it using Query Builder, I get something like the code below. What syntax do I need to replace the integer date code with to do this? The documentation is pretty sparse on this topic.

Query1 = New SQL Query(
 Version( 130 ),
 Connection(...),
  QueryName( "Generic Query" ),
  Select(
   Column( "AA", "t1" ),
   Column( "AB", "t1" ),
   Column( "BB", "t1" ),
   Column( "BC", "t1" ),
   Column(
    "TIMESTAMP",
    "t1",
    Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
   ),
  From(
   Table(
   "XXTABLE",
   Schema( "XX" ),
   Alias( "t1" )
   )
  ),
 Where(
  Contains(
   Column( "AA", "t1" ),
   "Stuff",
   UI( Contains( Base( "Categorical" ) ) )
  ) & Contains(
   Column( "AB", "t1" ),
   "Other Stuff",
   UI( Contains( Base( "Categorical" ) ) )
  ) & GE(
   Column(
    "TIMESTAMP",
    "t1",
    Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
   ),
   3605823947,
   UI( Comparison( Base( "Continuous" ) ) )
 );

gzmorgan0 · Answer

Cameron was correct. See the example below that verifies a variable can be used.

However, if  you are getting a deleted reference, my quess is that you are prompting the user for information, and you are not retrieving the information (stdt) correctly.

If you share your dialog, someone will help you with the extraction.

stdt = 3402950400;
show( As Date(stdt) ); // 01Nov2011;
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Stock Averages" =>
		"$Sample_Data\Stock Averages.jmp"]
	),
	QueryName( "SQLQuery1" ),
	Select(
		Column( "Date", "t1", Numeric Format( "m/d/y", "-1", "NO", "" ) ),
		Column( "Open", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "High", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "Low", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "Close", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column(
			"Moving Average",
			"t1",
			Numeric Format( "Currency", "2", "YES", "", "USD" )
		)
	),
	From( Table( "Stock Averages", Alias( "t1" ) ) ),
	Where(
		GE(
			Column( "Date", "t1", Numeric Format( "m/d/y", "-1", "NO", "" ) ),
			stdt,
			UI( Comparison( Base( "Continuous" ) ) )
		)
	)
) << Run

mwechtal · Answer

Just inserting StDt gets me:

deleted object reference[ ]

I can find a few references in the community that show something like the following, but I don't see anything specific to Oracle.

'" || StDt || "'

" || StDt || "

to_date('^StDt^','m/d/y h:m:s')

to_date('06/13/18 01:01:01','m/d/y h:m:s')

cwillden · Answer

What happens when you replace the integer date/time value (3605823947) with StDt?  Seems like that should work.