Parametrized the Date field in SQL query

Question

Hello Guys,

Looking for a help to parameterize the Date  field in my SQL Query.

I have Query like "Select * from TABLE_A where DATE_A='2-July-2019' and DATE_B between '1-July-2019 and '30-July-2019'

I am using a Input tool and running this query and loading the results back in the database.

Now i want to automate this process by removing the hardcoded Date Fields by using parameter provided in a separate file/Configuration Table.

DATE_A='2-July-2019'

DATE_B1='1-July-2019'

DATE_B2='30-July-2019'

I am thinking ,if its possible to Read the Config file/Table and read these values and then pass these value to my Query in Input Tool and then load the data into a table in Alteryx.

NicoleJohnson · Accepted Answer

Have you looked at using the Dynamic Input tool? Basically, you can read your file/configuration table to get your date parameters, and then replace a placeholder date in your Dynamic Input query to update to the new dates when the query is run.

Check out this Knowledge Base article for some tips on configuration. Dynamic Input is BY FAR one of my favorite tools for it's versatility in cases exactly like this!

NJ

geraldo · Accepted Answer

Hi.

There are 2 ways to solve:
dynamic input already mentioned and make the necessary adjustments.
Other:
Dynamic In-Db input using your already created connection
In a Text Input create the following columns:
column: Query1
and insert the query
Select * from TABLE_A where DATE_A = 'DATEA' and DATE_B between 'DATE1' and 'DATE2'
Columns
DATEA and insert 2-July-2019
DATEB1 and insert 1-July-2019
DATEB2 and insert 30-July-2019

in a formula tool do the replace:
create Query with the following calculation
Replace (Replace (Replace ([Query1], 'DATEA', [DATEA]), 'DATEB1', [DATEB1]), 'DATEB2', [DATEB2])

And in the Dynamic input In-Db tool pass the Connection and Query parameters and you're done.

Attached is an example

geraldo · Accepted Answer

Hi

I just gave you an example using Dynamic Input in-db  There are several ways you can solve the substitution of values, I'm moving faster for you to think and use creativity. I am sending the same example with dynamic input query.

geraldo · Accepted Answer

hi,

Here's the example of the way you want to do your workflow.