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]

Is there a way to have same user input fromt he text box work on another database SQL pull within the same script? The only catch is this new database follows traditional/standard numeric type date column. Is there a way to convert the user inputs (fromdate & todate) to secondarly variables with a numeric format month-day-year? Primary character based dates to be used for the issue that is already solved and secondary set to extract data based on numeric dates. 

Here is my SQL that I am trying to use for extracting yield data from another database. 

nw = new window("New Window for user input", << 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);

Yieldsql = evalinsert(
" SELECT    
        model,
        lot,
        wafer,
        COUNT(test_status_code) as BIN_COUNT
    FROM
        test_data
    WHERE
        purpose='PROD'
        AND test_data.test_date BETWEEN TO_DATE('^fromdate^', 'MM/DD/YYYY hh24:mi:ss') AND TO_DATE('^todate^', 'MM/DD/YYYY hh24:mi:ss')
        
    ORDER by lot, wafer");


dt = Open Database(	"Generic database connection string", Yieldsql, "Yield_Data");

[#3]

Fixed it by correcting the WHERE clause for test_date

AND test_data.test_date BETWEEN TO_DATE('^fromdate^', 'YYYY/MM/DD hh:mi:ss am') AND TO_DATE('^todate^', 'YYYY/MM/DD hh:mi:ss am')

Now I am able to use the same input date variables across 2 databases in the same query. One query pulls data with numeric dates and another sql pulls data where dates are character based.

[#4]

Thank you for the assistance. This worked perfectly!