Alter Date format for Date Interface Tool

Question

I have a sql query and i need to update a date in the format of 2020/3/11 but the tool updates with 2020-03-11 and no data gets pulled.

I think i could just "update with formula" but i'm not 100% sure how i get it to replace a specific string  (as is I just Update Value and Replace a Specific String)

Thanks in advance!

BenMoss · Answer

Yes, exactly that unfortunately, so your formula might be quite long, though it should be relatively simple as It's just a long string with a field placed somewhere within it.

Perhaps a better alternative would be to use this alternative method, which is to use the dynamic input tool, as outlined in this post: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynamic-Input-Tool/ta-p/17431

You could pass your date value into a text input, with a simple update value action, and then use a fornula tool to convert it to the desired format before then passing it into the dynamic input tool and replacing the relevant part of your input statement.

Ben

ChrisSpr · Answer

Yeah that's close.  I have a rather bulky sql query tho so I just update a specific string that's found within the query:

I'm not sure if i grok what you suggest as it seems like i'd have to put my entire query in a formula?

Thanks again for a quick response!

BenMoss · Answer

Hi @ChrisSpr it's hard to say without knowing what tool and the underling object is your looking to replace, but yes you are right this can be done with the "update with formula", I'll try and showcase an example.

Let's say your action is connected to the input tool and you have made a simple query like...

Select * From TABLE

WHERE "Field" >= '2020-03-11'

With the update with formula you would have to rebuild this entire string, which would be done like...

"Select * From Table

Where Field >= '"+[#1]+"'"

This would then update your whole statement with the value given in the date interface tool. The challenge is then to convert the date format given by the date format to the value required, which can be done with the datetimeformat() function, something like...

"Select * From Table

Where Field >= '"+datetimeformat([#1],%Y/%m/%d)+"'"

I hope this is useful, as stated if you can give us a bit more context about exactly what you need to overwrite with the action that would be useful.

Ben