Subscribe Bookmark RSS Feed

Having custom date range in SQL based on user input variable

SOLVED
bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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.

Date column sample.JPG

 

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
);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

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);
11 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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 || "'" )
	)
);
Jim
bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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

sol1 ip1.png

sol1 op1.png

 

Input type 2

 

sol1 ip2.png

sol1 op2.png

 

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?

sol1 error.JPG

mark_anawis

Community Trekker

Joined:

Nov 18, 2014

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
);

 

bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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

sol2 ip1.png

sol2 op1.JPG

bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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

 

Date column sample.JPG

mark_anawis

Community Trekker

Joined:

Nov 18, 2014

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

bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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

 

JMP alert.JPG

 

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?

 

Picture1.png

pmroz

Super User

Joined:

Jun 23, 2011

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);
bharathu

Occasional Contributor

Joined:

Jan 4, 2017

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