none
Date field in data driven subscription RRS feed

  • Question

  • Hello all!

     

    I am having a strange problem with Reporting Services on SQL 2005 SP2.

     

    My report has to parameters of type "date", the usual report_from and report_to. I can run this report ad-hoc without a problem, and also with a data driven subscription for email address and other parameters.

    However, once I want to make the date columns data driven the report does not process anymore (*1) and the RS error log says that there was a type mismatch / conversion error, the given value for report_from cannot converted to date.

     

    The sql query generating the dates does something like GETDATE() or DATEADD(DAY, -7, GETDATE()), meaning I only use operations that really work on date data types and return dates - and the report is also clearly set to date data type... there should be no conversion needed in the first place, and it surely should not fail...

     

     

     

    (*1) The report sometimes worked, that is what lead me to the discovery the the date is really converted to a text and then back into a date at some point.

    If I use GETDATE -7 for my "from" and GETDATE for my "to" to report a week, the report will work on say 9th of August. -7 days is 08/02/2008 so the report WOULD run from 2nd to 9th of August, perfect! What it does however is run 8th of February to 8th of September as our systems are irish and use dd/mm/yyyy - this are still valid dates but wrong of course.

    If you run the report on say 3th of August, you get something like 26th of July for 7 days back, 07/26/2008 and that does NOT convert back to even a wrong date -> Execption Type Mismatch is thrown!

     

    So, in short, even if your SQL query returns a DATETIME and your report takes a DATETIME there is a conversion to text and back in between, and there the dates get messed up. One conversion seems to be done US style hardcoded while the other seems to be "system settings depending" interpreteded.

     

    That's my analysis. What can I do? I would not really want to change my report parameters away from datetime since I cannot ask my users to enter 39123 to 39129 for this week and 39xxx for next week and so on.

     

    The solution in http://msdn2.microsoft.com/en-us/library/ms159825.aspx (define as YYYY-MM-DDTHH:MMTongue TiedS) is also not helping, this applies to calling the report from a URL where you can setup the date this way yourself. I need to run it as a data driven subscription.

    Shall I change the SQL query to return the date as text formatted as in the article, does that work? Anyone with any idea?

    We are running on clean SP2 here, if this is addressed in a hotfix please let me know!

     

    Thanks

    Ralf

     

    Monday, August 11, 2008 8:51 AM