Submit and vote on feature ideas.

Welcome to the new Parasoft forums! We hope you will enjoy the site and try out some of the new features, like sharing an idea you may have for one of our products or following a category.

current date parameterizing in the sql query

Options
mdsheeraz
mdsheeraz Posts: 17

Hi,

We are using a query to fetch the data from a table by hard coding a specific date in the query.
Is there anyway we can parameterize the date to pick the current date while execution and show the data for the same instead of hard coding the date.

sample query using in the data source step:
select * from input_table
where interval_date = '2024-05-14'

Answers

  • williammccusker
    williammccusker Posts: 649 ✭✭✭
    Options

    Hi,

    Are you using the DB Tool? If so then you could setup a Data Generator Tool, to create the current date in a variable and then parameterize the query using that variable.

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    I tried with data source step but couldn't resolve the variable, DB tool I tried now getting expected response.
    However, the requirement is to insert the extracted column values ( response received from a query) to the different DB table using insert function.

    Adding another DB tool i tired but it couldn't resolve the variables and fetching response.

  • williammccusker
    williammccusker Posts: 649 ✭✭✭
    Options

    Hi,

    Is the use case to first match some rows of data based on the incoming request plus the current date and then insert new rows based on the selected data? If the DB tool setup works for the initial query are you now stuck at the part of figuring out how to take the results and insert new data?

  • benken_parasoft
    benken_parasoft Posts: 1,243 ✭✭✭
    edited May 14
    Options

    I would first check if your DBMS supports something like a "CURDATE" or "CURRENT_DATE" function. For example:

    select * from input_table
    where interval_date = CURDATE()
    

    Otherwise, you can also use the "sv:date-math" inline expression:

    select * from input_table
    where interval_date = '${{=sv:date-math('', 'yyyy-MM-dd')}}'
    
  • williammccusker
    williammccusker Posts: 649 ✭✭✭
    Options

    From a message

    Yes, using DB tool setup worked for initial query. Now the results need to be inserted to different DB > table using insert function.

    I tired adding output step as XML data bank and extracted data source columns and same variables > are declared in the new DB tool with insert query but it didn't work.

    getting below error:
    org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "${interval_ts_date}"

    Do you have an example you can share that shows what tools are chained where? As long as the second DB tool comes after the Data Bank that is extracting values from the first DB tool the variables should resolve. There might be something in the hierarchy of the tools that might explain the issue.

    As mention in the previous comment another alternative might be to look for a function supported by PSQL that could be used instead of a variable.

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    Yes, DB tool setup worked to fetch the data for current data, now i am trying to insert that data to different DB table.

    One more step I tried by adding output step to DB tool as XML data bank and extracted data source columns and same variables declared in the new DB tool step using insert function but it didn't work.

    getting below error:
    org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "${interval_ts_date}"

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    Yes, using DB tool setup worked for initial query. Now the results need to be inserted to different DB table using insert function.

    I tired adding output step as XML data bank and extracted data source columns and same variables are declared in the new DB tool with insert query but it didn't work.

    getting below error:
    org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "${interval_ts_date}"

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    Yes, using DB tool setup worked for initial query. Now the results need to be inserted to different DB table using insert function.

    I tired adding output step as XML data bank and extracted data source columns and same variables are declared in the new DB tool with insert query but it didn't work.

    getting below error:
    org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "${interval_ts_date}"

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    Yes, using DB tool setup worked for initial query. Now the results need to be inserted to different DB table using insert function.

    I tired adding output step as XML data bank and extracted data source columns and same variables are declared in the new DB tool with insert query but it didn't work.

    getting below error:
    org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "${interval_ts_date}"

  • mdsheeraz
    mdsheeraz Posts: 17
    Options

    Thanks for your inputs, it worked by adding "CURRENT_DATE" function in the query.