Clarity

  • 1.  Crystal Command object with Date parameters

    Posted Jul 08, 2010 12:10 PM
    Has anyone had any luck creating a report that uses a command object for the data source and has date parameters evaluated in the command object?

    I can run my report from Crystal reports developer with no issues, but when I publish the report and execute it from Clarity I get an 'invalid date' error. I've tried everything from converting the date to a string and to a date again but with no luck. I can't seem to validate the exact SQL going to the database either.

    The expression is not complicated, it's roughly:
    Select * from cmn_sec_users where last_updated_date >= {?param_date}
     
    However, this simple command gives me the 'invalid date' error in the would-be report window when I run the report from Clarity. Again, the report runs in Crystal report developer (XI R2)

    I have successfully created reports for the same environment using stored procedures.

    Thanks,
    Halty


  • 2.  RE: Crystal Command object with Date parameters

     
    Posted Jul 12, 2010 08:58 PM
    Hi All,

    Any ideas here for Halty?

    Thanks!
    Chris


  • 3.  RE: Crystal Command object with Date parameters
    Best Answer

    Posted Jul 28, 2010 11:32 AM
    OK, so I answered my own question, and the solution is pretty obvious. The way the date parameters ought to be referred to is as a string . Then the SQL should have code to convert the string to a date value. Here's some sample SQL for getting records from the projects table:
    SELECT   *
      FROM   SRM_PROJECTS
     WHERE   CREATED_DATE = TO_DATE('{?p_date}', 'YYYY-MM-DD HH24:MI:SS')
    As you can see, my date parameter, ?p_date, is referenced as a string, due to the single quotes around it. It is then necessary to cast the string to a date in the DB's specific syntax (the sample above is for Oracle).

    Note: The above code is very simple and meant to illustrate the point only. It would not work well in real life because the date coming into the code (as ?p_date) would have to match the dates in the table exactly, all the way down to the second.


  • 4.  RE: Crystal Command object with Date parameters

     
    Posted Jul 28, 2010 05:52 PM
    Thanks for letting everyone know!
    Chris


  • 5.  RE: Crystal Command object with Date parameters

    Posted Aug 24, 2010 06:09 AM
    You can also just set the parameter type as datetime and then there's no need to do the conversion, Crystal works it out OK.