community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Time Date Stamp

Meteor

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.

Alteryx
Alteryx

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.

 

DateTimeAdd(DateTimeNow(),-4,"years")

Alteryx
Alteryx

You could also probably change the sql query with the dynamic input tool after calculating the date as seen here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynam...

Meteor

Brandon

 

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.

Alteryx
Alteryx

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

Alteryx
Alteryx

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

 

 

https://stackoverflow.com/questions/38221393/how-to-subtract-months-from-date-in-hive

Highlighted
Nebula
Nebula

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 

Dan 

Labels