Alteryx Designer Discussions

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

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Selecting data by date in the input tool

Highlighted
6 - Meteoroid

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

Highlighted
14 - Magnetar
14 - 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

Highlighted
6 - Meteoroid

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
11 - 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();

 

Highlighted
6 - Meteoroid

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?

Highlighted
11 - 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.

Highlighted
5 - Atom

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