Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Selecting data by date in the input tool

DataGrad
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

7 REPLIES 7
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

DataGrad
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)

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

 

DataGrad
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?

adm510
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.

neerajlko
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. 

latimo1
7 - Meteor

I figured this out today: date is 10-02-2023

 

Cast(Curdate() - 1 as Date),

Cast(Curdate() - 2 as Date),

Cast(Curdate() - 3 as Date),

Cast(Curdate() - 4 as Date),

Cast(Curdate() - 5 as Date)

 

Yields:

 

EXPR_1          EXPR_2          EXPR_3          EXPR_4            EXPR_5

2023-10-01  2023-09-30  2023-09-29  2023-09-28            2023-09-27

Labels