Parameter Input Date not accepting in SQL Query after running in Alteryx Analytic

[jayeshrc8545]

Hello,

I am not able to run input parameterized date  into SQL Query & giving following error:

Start: Designer x64: Started running C:\Users\chauhanja\Desktop\JRC\Alteryx\Commercial DB Queries.yxmd at 07/19/2023 17:53:08
Info: Designer x64: The Designer x64 reported: Running at a Low Priority.
Info: Designer x64: The Designer x64 reported: Allocating requested memory would be more than available physical memory. Reverting to 866.5 MB of memory.
Info: Designer x64: The Designer x64 reported: This is AMP Engine; running 2 worker threads; memory limit 866.5 MB.
Info: Input Data (46): Alias translated to odbc:DRIVER={ODBC Driver 17 for SQL Server};UID=LAVASTORM_USER;PWD=__EncPwd1__;DATABASE=FS92PRD;SERVER=awsprodpfsqlvs1.ent.foxtel.com.au|||SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT =  'First_Day_Of_Month'
--WHERE FXT_KEN_TRANS_DT = '2023-06-01'
Info: Input Data (46): ODBC Driver version: 03.80
Info: Input Data (46): Lua Script Loaded: sqlserver17.lua
Error: Input Data (46): Error SQL Execute: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.
End: Designer x64: Finished running Commercial DB Queries.yxmd in 1.7 seconds with 1 error

I am basically inputting date & update action to replace string below 

Replace([Destination],'First_Day_Of_Month', DATETIMEPARSE([#1],"Y%-m%-d%"))

This replaced date value will be will be feed under query below to retrieve records based on user inputs

SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = 'First_Day_Of_Month'
--WHERE FXT_KEN_TRANS_DT = '2023-06-01' 

Query is working fine with hard coded values in date as commented out..

Tried couple of options but that didn't work actually.

Any sort of help will be appreciated.

Regards,

Jayesh

[jayeshrc8545]

basically the below replace formula will check for string ''First_Day_Of_Month'' in destination & then replace them with input date after parsing them.

Replace([Destination],'First_Day_Of_Month', DATETIMEPARSE([#1],"%Y%-%m-%d")

Just need First_Day_Of_Month in query to be replaced by '2023-06-01' if i have selected 1st Jun '23 from date.

I am not sure where you trying to change in action tool.

Is there a way to test what i m trying to pass from input date go through action to get into text input tool or some other component.

Can you share the yxwz file which will help me in understanding in a better way.

[apathetichell]

In the action tool replace value with formula you replace the value with the formula - so if the query is 

select * from ... where my_date = '2023-07-01' 

and your formula is replace("2023-07-01","2023-07-01",[#1]) - your final value you are sending into your query is [#1] - not your original query with [#1] substituting for "2023-07-01" - whatever is created by your formula field is your full SQL expression. You can create your full query using either a replace("select * from ... where my_date = '2023-07-01' ","2023-07-01",datetimeformat([#1],"%Y-%m-%d") - or by building it like I showed in my prior comment.

[geraldo]

@jayeshrc8545 

este fluxo de trabalho que você enviou está na versão antiga. Eu fiz a correção e veja se funciona