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

Alteryx designer Discussions

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

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Selecting data by date in the input tool

Atom

Hi

 

I have a very large data source. so i am importing a part of it by specifying today's date in the Input Data, Table or Query, SQL Editor:

Select * From PUB.tr_hist Where PUB.tr_hist.tr_date >= '2017-10-09'

 

This works but i have to manually change the date every time I import the data. I want it to automatically select the data from today's date when it is run.

 

I have tried doing:

Select PUB.tr_hist.* From PUB.tr_hist Where PUB.tr_hist.tr_date >= 'DateTimeToday()'

But this does not work...(Both with and without the '   ' )

 

It would also be useful to do this with data form the last x number of days, as well as just today's date.

 

Thanks

Magnetar
Magnetar

I believe the issue is with the parameter you are using for DateTimeToday - that is an Alteryx function, not a SQL function. Try something like GETDATE(), which will return the current datetime (i.e. '2017-10-09 08:03:25')... or CONVERT(date,GETDATE()) should give you today's date formatted just as '2017-10-09' without the timestamp, if that's how the field you're filtering on is formatted...

 

And then if you wanted to select for the last few days, you can use the DATEADD function in SQL to subtract a few days from your desired start date for pulling data.

 

Does that help?


NJ

Atom

Does not seem to work...

 

Select PUB.tr_hist.* From PUB.tr_hist Where PUB.tr_hist.tr_date >= 'GETDATE()'

 

gets an error that Says: Invalid date string (7497)

Highlighted
Bolide

What database are you using? If you're going to call a SQL function, then you do not want to put it in quotes.

 

Depending on which db you're using, one of these might work:

Select PUB.tr_hist.* From PUB.tr_hist Where PUB.tr_hist.tr_date >= CURDATE();

Select PUB.tr_hist.* From PUB.tr_hist Where PUB.tr_hist.tr_date >= GETDATE();

 

Atom

Its an ODBC Database

 

The CURDATE() works thanks :)

 

How would i write the DATEADD function on to this to make it the past x number of days?

Bolide

ODBC isn't a database, it's a connection type.  You would need to find out the database type to determine how to write the SQL for a DATEADD fxn.

Can you help me as i am connecting input tool to Cosmos DB in Azure.

 

There it is pulling all data but i have to get data for last 1 week only. 

 

Select UATAmbulanceCollection.AmbulanceCollection.AmbulanceCollection.Submission_DateTime

where

UATAmbulanceCollection.AmbulanceCollection.AmbulanceCollection.Submission_DateTime

>=

CURDATE()-7;

 

 

Above query is working but the problem is date is in string . I have to convert it to Date.

 

Any help how can we convert it to Date. I have tried SQL Functions but it is giving error. 

Labels