current date parameterizing in the sql query

mdsheeraz

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'

williammccusker

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

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

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

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

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

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

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

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

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

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