Date functions that are supported in a straight SQL Server query are not supported when doing cross-queries.
Standard SQL function not supported in TDA:
and DC.CallTime >= ( now() - 30 ) looks back 30 days
This SQL function IS supported in TDA as a stand-alone SQL Server query
and DC.CallTime >= (getdate() - 30 ) looks back 30 days
Neither function is supported in TDA cross-query. When I used the built-in calendar function instead of the Formula option, I got results. I suspect the problems are with ODBC function support.
I need more granular control than the default calendar function that I ended up using, which has limited options.
1. Standard SQL syntax and functions should be supported
2. If there is a syntax error, there should be proper error messaging.
Different SQL databases use different Date/Time function and there are no common date/time functions. In a single connection mode user should use server specific functions. For a cross-query mode MySql engine is used so a user should use MySql date/time function to do date arithmetic.
In this particular case he should use
DATE_SUB(now(),INTERVAL 30 day)
The list of SQL Date Functions for different servers including MySQL can be found there
http://www.w3schools.com/sql/sql_dates.asp
An request as been submitted to prompt the user with this information.
