Where function and SQL functions Now() and Getdate() not working in cross queries (4261659)

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.