SSRS rdl preview shows date with bad value shows up despite SQL query that should exclude it

Question

SSRS rdl preview shows date with bad value shows up despite SQL query that should exclude it.

The xml for the query in the rdl-file is:

  <Query>
    <DataSourceName>ourDataSource</DataSourceName>
    <CommandText>
      use ourDataBase
      ----------------------------------------
      select convert(date, Date1) as ourDate1, count(*) as ourCountForDate
      from ourDataBase.dbo.ourTable
      where
      (
          (Date1 between (getDate()-365) and (getDate()+1) )
      )
      group by convert(date, Date1)
      order by convert(date, Date1)
    </CommandText>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>

This query works just fine in the SQL database when using TSQL directly in SSMS.

However, there is a stray value with a strange year in the database (such as 7654), and it shows up in the results for the rdl file in 'preview' but NOT in the SQL-query results in SSMS.

I have tried lots of variants, with greater than or equal to, and so forth, but this stray value always shows up.

I am using SSRS, Visual Studio 2015, with underlying TSQL. The SSRS uses 2016/01/reportdefinition. The SQL server for the project is SQL Server 2008 R2, 2012 or 2014.

Any ideas?

Answer 1

It's better to create a stored procedure in SQL Server and then have the SSRS call that. Can perform better too. This keeps the data control entirely in the SQL Server.