Filtering by date In-db vs. regular filter tool

[msoysal]

Hi all,

Below is the expression I placed in the regular filter tool, and I was able to run with no issues. When I type the exact same expression In_DB filter, it did not run.

I tried so many combinations of the syntax including (), “ ”, ‘ ‘, ToNumber etc. but no luck. The date fields I am hitting in the sql server database are in “date” format so I assume I do not need to make any conversions. Help is appreciated to replicate what I do with regular filter in  In_DB filter tool.

DateTimeDiff([a.sales_date],[b.recognition_date],"days") <=120

[LordNeilLord]

Hey @msoysal

InDb uses the native language of the database you are connecting to.

For example if you are connected to a SQL server you would need to do something like:

DateDiff(day, a.sales_date, b.recognition_date)<=120

For SQL reference you can look here: https://www.w3schools.com/sql/func_sqlserver_datediff.asp

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[msoysal]

Thank you for a great information. It got me closer to the solution but, still struggling to get the right syntax for the filter tool. Below is the simplified mock up query I ran in sql server management studio, and it ran perfectly. I got all the results I need.

Select DATEDIFF(day, a.sales_date, b.recognition_date)

From a

INNER JOIN b

ON a.xyz_id = b.xyz_ID

Where ...= '…'

;

 However, I still cannot get the right script to run on InDb filter tool. Here are the few syntax examples I tried with no result;

DATEDIFF(day, a.sales_date, b.recognition_date)<=120

DATEDIFF(day, a.sales_date, b.recognition_date)<='120'

DATEDIFF(day, a.sales_date, b.recognition_date)<="120"

DATEDIFF(day, "a.sales_date", "b.recognition_date")<=120

DATEDIFF(day, "a.sales_date", "b.recognition_date")<='120'

DATEDIFF(day, 'a.sales_date', 'b.recognition_date')<=120

DATEDIFF(day, 'a.sales_date', 'b.recognition_date')<=120

DATEDIFF(day, 'a.sales_date', 'b.recognition_date')<='120'

DATEDIFF(day, 'a.sales_date', 'b.recognition_date')<="120"

and all combinations with CAST(DATEDIFF(...) to INT) <= ... but still no luck. I am wondering what am I missing...

Thank you!

[LordNeilLord · Accepted solution]

Hey @msoysal

This is the correct syntax: DATEDIFF(day, "a.sales_date", "b.recognition_date")<=120

Here's it working with SQL Server:

Are you definitely getting values back when you run it in SQL Studio?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[msoysal]

Thank you for your time and effort on this. Yes, I do get the results. Here are the top 10 records (directly coppied & pasted) from my query output.

(No column name)
1 48
2 50
3 48
4 109
5 109
6 273
7 117
8 117
9 117
10 50

[LordNeilLord]

Hmm, interesting...

Maybe try creating the datediff in your select query (like you did in management studio) DateDiff(day,"Order Date", "Ship Date") As "DateDiff"

And then use this new column as the filter column?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[msoysal]

Hey,

You're not going to believe this; I had some supposedly "inactive" commentary down below the expression in the filter tool. I removed those with the syntax you've provided and it worked!  It looks like if any commentary is placed with open end notation (/*) with no closing (*/) it halts the process, even though the color of the comments is "green".

I really appreciate your help!

Thank you! 

[LordNeilLord]

Hey @msoysal

Haha, what a nightmare!! At least you've got it working now...that's the main thing :)

Glad I could help, I'd appreciate an accepted solution when you get a chance :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[Joshthomson]

I need the filter to either filter out any dates in colume Max_Profile_Date_Time to contain only dates from 2019-07-10 or just make it so it only gives me dates that are equal to 2019-07-10 

Please help