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

[geraldo]

@jayeshrc8545 

In this print that you sent, you don't show the principal which is what the actions tool is doing replace. send a print with the action tool configuration

[jayeshrc8545]

Please find attached snapshot for Replace Function used in Action Tool.

Also i did some tweaks in final dyamic output but it is throwing error below:

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 = to_date('First_Day_Of_Month','yyyy-MM-dd')
--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')
Info: Input Data (46): ODBC Driver version: 03.80
Info: Input Data (46): Lua Script Loaded: sqlserver17.lua
Error: Input Data (46): Error opening "SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = to_date('First_Day_Of_Month','yyyy-MM-dd')
--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')": No Columns Returned.

Attached error snapshot as well.

Final Dynamic Query  

SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = to_date('First_Day_Of_Month','yyyy-MM-dd')
--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')

The commented where clause is working fine 

Function used in Action Tool:

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

Let me know if you need additional clarification 

[geraldo]

@jayeshrc8545 

Your configuration is wrong.
I believe that the action tool configuration should be like this

[jayeshrc8545]

Hi Geraldo,

sorry for late response.

I ran query with your suggestion but getting below error: 

Error: Input Data (46): Error opening "SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = to_date('First_Day_Of_Month','yyyy-MM-dd')
--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')": No Columns Returned.

Same query works fine for hardcoded dates which has been commented in query  

SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = to_date('First_Day_Of_Month','yyyy-MM-dd')
--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')

[geraldo]

@jayeshrc8545 

Note that the configuration I gave you is Update value (Default) and not Update value with formula as your action tool was configured.

[jayeshrc8545]

I have used Update Value in the action tool as you suggested & it was showing error which i mentioned in comment before.

I am running the Alteryx analytic tool, selecting date & while going into action it should replace the string which its not doing it.

Kindly help on it.

[apathetichell]

This thread has gone way too long without anyone mentioning that Replace([Destination],'First_Day_Of_Month', DATETIMEPARSE([#1],"Y%-m%-d%") should be Replace([Destination],'First_Day_Of_Month', DATETIMEPARSE([#1],"%Y%-%m-%d")

[jayeshrc8545]

Hi both,

I have attached alteryx dev file for reference to get better idea on what i am trying to achieve.

Basically the First Day of Month should get replaced by selected date (Enter First Day of Month) in the query. 

[apathetichell]

change your action tool to read:

"SELECT * FROM FS92PRD.dbo.PS_FXT_KEN_ACCT_LN
WHERE FXT_KEN_TRANS_DT = to_date('"+datetimeformat(datetimetrim([#1],,"firstofmonth")"%Y-%m-%d")+"'--WHERE FXT_KEN_TRANS_DT = to_date('2023-06-01','yyyy-MM-dd')"

basically you are replacing your entire query with a date. that is not what you are trying to do.