Having custom date range in SQL based on user input variable

[#1]

Hi,

I am having issues while trying to have a custom user input based variable into my SQL code. The problem with the database I am connecting is that the date column is character. So here is the actual code that works well with hard coded dates inside the SQL query.

Open Database("Generic database connection string",
 " SELECT
 p.productname,
 p.model,
 p.family,
 TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime
 FROM database p
 
WHERE 
 txntime >= '20161201 000000000'
 AND txntime < '20161231 000000000'
 ", 
 // End of SQL statement
"New_Table" // New table name
);

The picture below shows the sample view on how the dates are saved in this table. Column property is Character & Nominal.

Here is the code I tried to insert user input variable. This code returns an empty table. 

Delete Symbols();
Delete Globals();
nw = new window("Scrap Data", << modal(),
    panelbox("Enter Date Range",
       hlistbox(
            text box("From Date"),
            fromdate_teb = text edit box("%", << set width (100)),
        ),
        hlistbox(
            text box("To Date"),
            todate_teb = text edit box("%" , << set width (100)),
        ),
                
        ),
    panelbox("Actions",
        hlistbox(
            ok_button = button box("OK",
                        fromdate = fromdate_teb  << get text;
                        todate = todate_teb << get text;
                        ok_pushed = 1;),
            cancel_button = button box("Cancel", ok_pushed = 0),
        )
    )
);

Open Database("Generic database connection string",
      " SELECT
       p.productname,
       p.model,
       p.family,
       TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime
  FROM database p
      
WHERE     
       txntime >= '^fromdate^'
       AND txntime < '^todate^'
      ",    
        // End of SQL statement
"New_Table" // New table name --> can be changed as per requirements
);

[#2]

Try this and see if it works for you

Names Default To Here( 1 );
Delete Symbols();
Delete Globals();
nw = New Window( "Scrap Data",
	<<modal(),
	Panel Box( "Enter Date Range",
		H List Box( Text Box( "From Date" ), fromdate_teb = Text Edit Box( "%", <<set width( 100 ) ), ),
		H List Box( Text Box( "To Date" ), todate_teb = Text Edit Box( "%", <<set width( 100 ) ), ), 
                
	),
	Panel Box( "Actions",
		H List Box(
			ok_button = Button Box( "OK",
				fromdate = fromdate_teb << get text;
				todate = todate_teb << get text;
				ok_pushed = 1;
			),
			cancel_button = Button Box( "Cancel", ok_pushed = 0 ),

		)
	)
);

Eval(
	Substitute(
			Expr(
				Open Database(
					"Generic database connection string",
					" SELECT
       p.productname,
       p.model,
       p.family,
       TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime
  FROM database p
      
WHERE     
       txntime >= __From_Date__
       AND txntime < __To_Date__
      ",    
        // End of SQL statement
					"New_Table" // New table name --> can be changed as per requirements
				)
			),
		Expr( __From_Date__ ), Parse( "'" || fromdate || "'" ),
		Expr( __To_Date__ ), Parse( "'" || todate || "'" )
	)
);

[#3]

Hello Jim,

Thanks for the reply, but I am still having issues. Here are the snap shots of my input and the corresponding errors seen in the log

Input type 1

Input type 2

I have a feeling the problem is coming from this part of the code but I am still a learning JSL. I would appretiate feedback from experts like you. Is there any way that we can directly use the user input variable "fromdate" & "todate" directly in SQL?

[#4]

I think you need to enforce the format for the from date and to date which the user enters so I've provided an option. However, I think the major issue is in the where statement so I've provided an option which should get you close to what you need.

Delete Symbols();
Delete Globals();
nw = new window("Scrap Data", << modal(),
    panelbox("Enter Date Range",
       hlistbox(
            text box("From Date"),
            fromdate_teb = text edit box(format(today(),"y/m/d h:m:s"), << set width (100)),
        ),
        hlistbox(
            text box("To Date"),
            todate_teb = text edit box(format(today(),"y/m/d h:m:s") , << set width (100)),
        ),
                
        ),
    panelbox("Actions",
        hlistbox(
            ok_button = button box("OK",
                        fromdate = fromdate_teb  << get text();
                        todate = todate_teb << get text();
                        ok_pushed = 1;),
            cancel_button = button box("Cancel", ok_pushed = 0),
        )
    )
);

Open Database("Generic database connection string",
      " SELECT
       p.productname,
       p.model,
       p.family,
       TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24:mi:ss') txntime
  FROM database p
      
WHERE     
       txntime >= to_date('"|| fromdate || "', 'yyyy/mm/dd hh:mi:ss am')
       AND txntime < to_date('"|| todate || "', 'yyyy/mm/dd hh:mi:ss am')
      ",    
        // End of SQL statement
"New_Table" // New table name --> can be changed as per requirements
);

[#5]

Hello Mark,

Thanks for your reply as well. I am having issues with this code as well. 

Here are the snap shots of my user input in the text box and the error that I am seeing in the log

[#6]

The biggest reason for this problem is that the database I am dealing with has time stamp is a character format. :(

[#7]

The date and format of the date in the TO_DATE function need to match.

Try this in the select section of the script I sent:

TO_DATE(
SUBSTR (txntime, 1,4)||"/"||SUBSTR(txntime,5,2)||"/"||SUBSTR(txntime,7,2)||" "||
SUBSTR(txntime,10,2)||":"||SUBSTR(txntime,12,2)||":"||SUBSTR(txntime,14,2),
'yyyy/mm/dd hh24:mi:ss') txntime

[#8]

Thanks Mark but unfortunately I am seeing the following error when trying to execute

The error seems to be caused due to the concatenation of the space between the date & the time (highlighted in red). Is there any other way to have the space in between the date & the time? 

TO_DATE(SUBSTR (txntime, 1,4)||"/"||SUBSTR(txntime,5,2)||"/"||SUBSTR(txntime,7,2)||" "||SUBSTR(txntime,10,2)||":"||SUBSTR(txntime,12,2)||":"||SUBSTR(txntime,14,2),'yyyy/mm/dd hh24:mi:ss') txntime

Also I have a question about the text 'am' for the where clause. Could you let me know why is it needed there?

[#9 · Accepted solution]

You have to match the hatch for your different dates.  The dates coming from JMP have one date mask, while the dates in your database have another one.  This should do it:

nw = new window("Scrap Data", << modal(),
    panelbox("Enter Date Range",
       lineup box(ncol(2),
            text box("From Date: "),
            fromdate_teb = text edit box(format(today(),"y/m/d h:m:s"), << set width (150)),

            text box("To Date: "),
            todate_teb = text edit box(format(today(),"y/m/d h:m:s") , << set width (150)),
        ),
    ),       
    panelbox("Actions",
        hlistbox(
            ok_button = button box("OK",
                        fromdate = fromdate_teb  << get text();
                        todate = todate_teb << get text();
                        ok_pushed = 1;),
            cancel_button = button box("Cancel", ok_pushed = 0),
        )
    )
);
print(fromdate, todate);

sql = evalinsert(
"SELECT
       p.productname,
       p.model,
       p.family,
       TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') txntime
  FROM database p
 WHERE TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') >= to_date('^fromdate^', 'yyyy/mm/dd hh:mi:ss am')
   AND TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') < to_date('^todate^', 'yyyy/mm/dd hh:mi:ss am')");

Open Database("Generic database connection string", sql, "New_Table");
print(sql);

[#10]

Thank you so much. This script is working great and I am getting the data.