SSRS Query doesn't work but it does in SQL, why?
Does anybody know why the following SSRS 2008 query in designer doesn't work but I copy the same script into SQL 2008 Server Mgmt Studio and it does work without error? The error it gives me when I run it in SSRS is "an error occurred while reading data
from the query result set. The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value". The problem SADates is a text field that is supposed to hold 2 date values that look like this 01/01/12 - 02/01/12 so when it
takes the LEFT 8 characters and they are not a date it pukes with the error message. But I can copy this exact code into SQL and it runs no problem, no errors. They have some goofy dates such as 01-01/12 or 01-01-12 and it doen't like then in SSRS but they
are ok in SQL. Anybody with the experience and knowledge as to why this is, please respond. Thank you in advance!
SELECT a.UserName, a.ProjectNumber,
MAX(CASE WHEN DATEDIFF(m, CAST(LEFT(a.SADates, 8) AS smalldatetime), a.MonthEndingDate) + 1 = 0 THEN 0
ELSE ROUND(ISNULL(CAST((100/ (DATEDIFF(m, CAST(LEFT(a.SADates, 8) AS smalldatetime), a.MonthEndingDate) + 1) * a.ContractInMonths) AS money), 0), 2)
END) AS StaffingProjection FROM tbl_Rpt_DFSM a
GROUP BY a.UserName, a.ProjectNumber, a.SADates, a.TotalUsed, TotalBudgeted, a.MonthEndingDate, a.ContractInMonths
June 4th, 2012 2:38pm
Have you tried creating this query as a stored procedure?
Also, I suggest to try
case when ISDATE(left(a.SADates,8)) = 1
etc. and also cast to DATETIME instead of SMALLDATETIME just in case - should not be any difference as how to cast.For every expert, there is an equal and opposite expert. - Becker's Law
My blog
Free Windows Admin Tool Kit Click here and download it now
June 4th, 2012 3:03pm
Hi There
I could not regenerate your issue in my test environment. I hope if you deploy this report this report might work on report server. The date format in Preview mode in Report Designer is determined by the culture of the operating system, while the date format
in the report manager depends on the culture of the browser.
Please have a look on this thread
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/32b62d3c-9a32-41bd-b7ad-95c3b89a7668
If you have any questions please ask.
Many Thanks
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
June 4th, 2012 5:13pm