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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics