BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
bharathu

Community Trekker

Joined:

Jan 4, 2017

Re: Having custom date range in SQL based on user input variable

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

 

bharathu

Community Trekker

Joined:

Jan 4, 2017

Re: Having custom date range in SQL based on user input variable

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.
Bandfield

Community Trekker

Joined:

Sep 5, 2017

Re: Having custom date range in SQL based on user input variable

Thank you for the assistance. This worked perfectly!