In an SSRS 2008 report, I am not getting some records selected when I run the report in the ssrs 2008 report.
However when I run the sql in ssis manager, the records are selected. Thus I am trying to determine why some of the records are not selected whe running the ssrs report.
In the table called 'transactionfile', the fields that I am trying to determine where there is a problem is the following:
SchoolNumber (varchar(50),null), and
Examples of data are SchoolNumber = '121' and TransactionPaymentDate = '04162014'
In the SSRS report, the parameter value for @SchoolNumber is text and the parameter value for @EndDate and @StartDate is date/time.
The following is the query I am having a problem with:
CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring (TransactionPaymentDate,1,2)+ '-' +
substring(TransactionPaymentDate,3,2)) as TransactionPaymentDate
from [eF].[dbo].[transactionfile] f
on rtrim(ltrim(s.SchoolNumber)) =rtrim(ltrim(f.SchoolNumber))
where rtrim(ltrim(s.SchoolNumber)) = rtrim(ltrim(@SchoolNumber))
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date)
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date) <= @EndDate
Thus could you suggerst what could be wrong when selecting some records by date?