Parametrized the Date field in SQL query

[UpendraPandey]

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 solution]

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 solution]

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

[UpendraPandey]

@NicoleJohnson 

Thanks for you response. It worked the way i wanted, but the only problem i am seeing it when i am pasting my query in Dynamic Input Query - I have to provide a hard coded date in my Query and then i am replacing with the date i want otherwise Alteryx is not validating the query.

In my Dynamic input i am replacing '2-July-2019' to '2-Aug-2019'.

Select * from TABLE_A where DATE_A='2-July-2019' and DATE_B between '1-July-2019 and '30-July-2019'

Is there any way where i can put the query like this in Dynamic Input

Select * from TABLE_A where DATE_A='$DATE_A' and DATE_B between '$DATE_B_1 and $DATE_B_2'

so that Alteryx can validated this. And then i will always replace '$DATE_A with some value. Otherwise i need to relplace one hard code value with another always.

[geraldo · Accepted solution]

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 solution]

hi,

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

[UpendraPandey]

@geraldo 

Awesome, Thank you

[datamodeling101]

How would this work with a calendar date object and action instead of using a text input?