How to use Interface tools to update date value in my SQL command (in Input Tool)

[RaghavanR]

Hi,

I am using an input Tool to retrieve data from Teradata using SQL query on the basis of Start date and end date. I want to get these dates as and when the query is run. I saw about using Interface tool and tried them but it is not working even asking me for the dates. Can some one please guide me where i am going wrong.

[AkimasaKajitani]

Now your setting is overwrite " replace([Enter Start Date],'01/01/2016',[#1]) " into [#1] because you check the Replace a specific string option.

If you check the Replace a specific string option, you can't use formula.

When you want the option, you type replace text( eg. 01/01/2016 ) into the text box.

If you want use formula, you select the action "Update Value with Formula".

[RaghavanR]

Thanks for the help. I have started working in alteryx only in the last 3 months and starting to handle bit complex issues. I have changed as per your suggestion. but i am getting an error in update value action. Can you please help me understand where am i going wrong?

Is this the best approach? or can you please suggest me any other approach?

[AkimasaKajitani]

Your formula is wrong.

Maybe the correct is below.

Replace([Destination], "01/01/2016", [#1])

The specification of Replace function is below. 

Replace(String, Target, Replacement)

You should enter "[Destination]" to "String" field.

[AkimasaKajitani]

I forgot to mention one thing.

Alteryx's Date tool outputs the value in standard alteryx format.

You need to format it into a format that the database use from standard alteryx format.

Maybe, the below function will work.

Replace([Destination], "01/01/2016", DateTimeFormat([#1],"%m/%d/%Y"))

If the month and day are reversed, then you will reverse them(%m and %d).

[RaghavanR]

Thanks again for your help on this but unfortunately still the query is picking up from the initial date in the database and not from the date i am giving. I have updated the screenshots of SQL query and action tool for your reference.

Sorry for being a pain.

[grazitti_sapna]

Hi @RaghavanR , try passing the formula like this 

Replace([Destination], '01/01/2016', DateTimeFormat([#1],"%m/%d/%Y")) I have replaced the double quotes to single quote.

Thanks.

[RaghavanR]

Thanks Sapna, Still I am facing the same issue even after changing to Single Quotes. I herwith attached my test workflow for your reference. not sure where i am going wrong.

[AkimasaKajitani · Accepted solution]

I fixed the below.

Please try to use it!

[grazitti_sapna · Accepted solution]

Hi @RaghavanR , I have made some changes in your action tool.

Try this if it works.

Thanks.