Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!

Time Date Stamp


Hey everyone!


Got a question and seems none of my other colleagues have opted out to find a solution by just hard coding dates.


In the Query Build I have a table and one of the columns is a VARCHAR date, example 2019-10-07.  


Now I've built my query but what I want to do is this:


Select  column 1, column 2, Column 3 (my VARCHAR Date)

From Table_Name

Where Column 3 >= Current Time Date Stamp - 4 years


It's the "Current Time and Date Stamp - 4 years" that I'm having the problem with.  I've been to several sites looking for the correct syntax on how to do that to no avail!


Can anyone provide an example.  It'd cut down my run time to under 5 minutes.


DateTimeAdd(DateTimeNow(),-4,"years") will give you 4 years prior to today's date. If you have your query in a macro, you could use the interface tools to pass this value dynamically into your SQL query

Alteryx Certified Partner
Alteryx Certified Partner

it may seem odd, but use the "DateTimeAdd(" function with "-4" years.




You could also probably change the sql query with the dynamic input tool after calculating the date as seen here:




Should have mentioned I was using Horton Works Hadoop!  However, that said your post triggered another thought and I tried this:

column_3 >= current_timestamp


I did my test query and I did not get an error.  I got a successful connection and compile of the query!

OK, now do you know the correct syntax for subtracting out four years from "current_timestamp"???  I tried just "-4" and it told me I had the wrong argument.


My thought was more about using a placeholder value in your query that is dynamically replaced in an Alteryx workflow using a value calculated from a formula. After creating the desired date in the proper format in Alteryx, you could pass that through to your query using a dynamic input tool that replaces the placeholder text in the where clause of your statement. 


Example Query.png


I'm a bit rusty in Hadoop, but you might be able to use this in your query instead


select add_months(current_date,-48);


I think you can use the add_months function in a hadoop query similar to how datetimeadd() works in Alteryx


Hi @Tim_at_Ford 


Similar to @BrandonB's suggestion there's also this function to subtract days


date_sub(timestamp startdate, int days) 


taken from this article and also here