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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Dynamic Date in SQL Editor/Visual Query Builder

Highlighted
Meteoroid

Hello,

 

I have a 2-digit field related to month and would like to have the query only return results matching last month (currently it's October, so I would want to have results corresponding to "Where xx.xxxxx_xxxxx.MONTH = 09". Can this be set up as to not have to manually update the query every month? I'm running into errors everytime I try to set up the SQL statement. There are some other fields and workflows that I'm having similar issues with, but I figure I can work on those once I've got this one cracked. 

 

If the results are filtered after the input tool then the workflow takes an impractical amount of time to run (hours).

 

Thanks in advance for any help.

Alteryx
Alteryx

I think you could use SQL syntax like:

 

Where xx.xxxxx_xxxxx.MONTH = MONTH(GETDATE())-1

That will only return the number, so if you need the zero padding it gets a bit more complicated with:

 

Where xx.xxxxx_xxxxx.MONTH = RIGHT(CONCAT('00', MONTH(GETDATE())-1), 2)

 

Meteoroid

Thanks for the quick response Joe

 

I get a Test Query Error with both versions. The message is "Error opening "Select.....". 

 

The field is formatted with leading zeroes, but they don't appear to be necessary for this purpose. Same results are returned either way. 

Alteryx
Alteryx

Hi @hgturxu

 

Are you able to post up your whole SQL statement? Also the version of SQL you are using?

 

Thanks

Joe

Meteoroid

Turns out this is working with IBM DB2.

 

The following works:

Where xx.xxxxx_xxxxx.MONTH = Month(Current_Date - Day(Current_Date) Days)

 

Alteryx
Alteryx

Got you, that will be why the SQL syntax wasn't quite working.

 

Glad it gave you enough to change up to DB2 syntax though :)

Meteor

Hi @JoeS  and @hgturxu 

 

I am having the same error with syntax in IBM DB2 but I have a full Date column, what should I query to have date field for rolling last 3 months?

Meteor

I found a solution : 

MyDateField >= Current_Date - 3 Months

Labels