Using variables for dates in the SQL editor

datamodeling101

I have several hard coded SQL statements in an input data that have a WHERE clause to limit the data between two dates i.e. WHERE DateField BETWEEN '2019-01-01' AND '2019-12-31'.  

Is it possible to create a variable in place of dates in the between statement for each SQL statement in the SQL editor?  That way instead of hard coding the dates to pull different years in each statement I can just update the variable for beginning and end dates and all SQL statements will be updated?  i.d.

WHERE DateField BETWEEN [Variable1] AND [Variable2];

I'm new to Alteryx.  If there is a better way to do the above without using variables how would I go about it?

Claje

Hi,

Roughly there are three good ways that you can go about this.

Method 1 is as @Thableaus suggested using Dynamic Input, something like the below screenshot:

Another option, which I wouldn't recommend unless you need other users to be able to run the process, is an Analytic App.  These are great for letting others run the process, but a little inconvenient if you need to be able to test or debug code changes.

Finally, you can make use of User Constants.  These are set in the Workflow Configuration tab.  For example, you could create one that is "StartDate" and one that is "EndDate" and populate them.  You can then update your WHERE query to be something like:

WHERE DateField BETWEEN '%User.StartDate%' AND '%User.EndDate%'.

This will automatically be replaced on runtime, so all you have to do is change the value of the two user constants in the Workflow-Configuration tab.  This is probably the "safest" option, in that you can continue using all the tools you normally use, and it will only replace EXACTLY the data you've specified, although it is the least dynamic for future efforts, like, say, making the workflow into an App or sourcing your dates from another file or database.