why the outcome data is different between "in Database" and "not in database"

[ll1100000]

I am trying to pull out all the date that smaller than Today's date

Here is the SQL that I am using which works perfectly..

SELECT *

FROM CA_AB_CM.CALENDAR_DAY

WHERE Fiscal_Date < DATE

AND Fiscal_year = 18

When I try to pull out the same data from Alteryx under the "in-database" function, here is what I keyed in in the "filter" function and the outcome data is exactly same as the above SQL query

"Fiscal_DATE" < date

"FISCAL_YEAR" = 18

However, when I try to pull out the same data not in "in-database" , the result is DIFFERENT...

Here are the data type

Fiscal_Date  === Date

Fiscal_YEAR === Int 16

When I try to key in this query into the filter function, it tells me "Parse Error at Char(20)  Unknow Variable "date"

[Fiscal_DATE] <= date

[FISCAL_YEAR] = 18

It seems like Alteryx can't recognize the date so it bring all the dates out.....

Although when I use the Basic Filter function by selecting [Fiscal_DATE] , it gives me the calendar for me to select the date that I want and I can select today's date which will give me the same result.

However I want to use a formula to always pick up any date smaller than today's date without adjust it...

Why the same formula works differently under "in-database" and outside databse?

My Alteryx version is 10.4

Thank you.

[DanS · Accepted solution]

Try using :

[Fiscal_DATE] <= DateTimeNow() 

AND [Fiscal_Year] = 18

[ll1100000]

Hi DanS;

Thank you for your help. This formula works!!!

[Fiscal_DATE] <= DateTimeNow() 

AND [Fiscal_Year] = 18

Now, I want to add another filter to get the 1st day of the current month...

I tried your formula that you replied to me in another post, but I continue getting syntax error" expected something between '(' and')'

here is how I mimic your formula

Datetimetrim([Fiscal_DATE], 'month')=Datetimetrim(datetimefirstofmonth(), 'month')

[DanS]

Putting this into the Filter Tool Custom Filter should work for you:

[Date] = DateTimeFormat(DateTimeFirstOfMonth(), "%Y-%m-%d")

[ll1100000]

Hi Dans  @DanS

I think "date" is always the nightmare for me when using Alteryx....

I try to set up the system automatically pull out yesterday's data everyday by using this formula

[Date] = date-1   

Again, this works great when using it under "in database", but doesn't wok under "not in database"

So I tried a few solution as below, but none of them works......Do you have any idea to enter the proper formula ???

[date]=datetimetoday()-1

Error: invalid type in subtraction operator

[Date] =  DateTimeFormat(tostring(tonumber(datetimetoday())-1), "%y-%m-%d") 

This one runs through successfully and didn't pops up any error, but didn't generate any output data..

Thank you.