0 Replies Latest reply on Nov 1, 2012 11:19 AM by Craig Willis

    Error with Week Number on ODBC live connection

    Craig Willis

      I'm connected live to an ODBC data source and I seem to be getting a consistent error around 2 things.  I have encountered this error in multiple tables in the data source.

       

      1.)  Relative date filters, every time I try to use a relative date filter I get an ODBC escape convert error.  This happens regardless of which level of relative date filter I try to use (day/week/month).

       

      and

       

      2.)  In an attempt to overcome this issue I attempted to use an IIF statement to to return true/false if the date part week numbers were the same.

      IIF(DATEPART('week',[patdateadded])=DATEPART('week',NOW()),TRUE,FALSE)

      When I drop this calculated field into the filter shelf I get the following error

       

      ERROR: syntax error at or near "=";

      Error while executing the query

       

      Unable to properly calculate the domain for the field 'Added this week'. Displayed data may be incorrect.

       

      I can use a DATEDIFF('day') formula and just filter all values that are greater than 7, the problem is then I get a rolling 1 week number as opposed to THIS weeks data. 

       

      Do I have an inherent issue in my data source or am I approaching this problem in the wrong manner.

       

      Thank you