Problem with Filtering on Tablix control using datetimeoffset data type

• edwardwill

  SSCertifiable

  Points: 5788

  More actions

  August 28, 2014 at 10:32 am

  #397272

  We recently changed the datetime columns in a client's database to datetimeoffset. There is an existing report with filters on the tablix control, and filters on grouping, which compare a column returned from the dataset with a parameter. In this instance the dataset column used to be of type datetime, but now is of type datetimeoffset, while the parameter is of type datetime (as Report Builder 3.0 doesn't provide a datetimeoffset type).

  When I first run the report I get an error:

  The processing of FilterExpression for the tablix ‘Tablix1’ cannot be performed. Cannot compare data of types System.DateTimeOffset and System.DateTime. Please check the data type returned by the FilterExpression. (rsComparisonTypeError)

  I've tried amending the tablix filter to extract the datetime part of the datetimeoffset value, by editing the expression to use

  Fields!MyDateTime.Value.DateTime

  but unfortunately I then get the error:

  Failed to evaluate the FilterExpression of the Tablix ‘Tablix1’. (rsFilterEvaluationError)

  Am I right in thinking that support for the datetimeoffset data type in Report Builder 3.0 is patchy? The report works if I cast the datetimeoffset column to a datetime in the embedded SQL, but this rather negates the point of using datetimeoffset data types.

  Hope someone can help!

  Thanks

  Edward