Crystal Reports.Net Invalid Parameter - String to date

Question

I am using the crsytal reports in VS2008. I want to be able to filter the dates in my crystal report viewer depending on the input of the user through a datetime picker. My problem is the date field itself in my database is in STRING format. -_- Whenever I compare values through code, I parse it to datetime.

How do I fix this? Is there any way I could do a parse of the report field? I don't want/can't change my database field into datetime because that would entail altering my whole system.

~ EDIT ~

Upon clicking the filter button I have a datetime picker named FromCreated_DTime. I parse its value into short date string (ex. 1/01/2011) and then assign it to my string parameter field. Using the Select Expert formula, I applied your code. My parameter field is named actualStart:

Date (ToNumber (Right ({Projects.Actual_StartDate}, 4)),
  ToNumber (Left ({Projects.Actual_StartDate}, InStr ({Projects.Actual_StartDate}, "/")-1)),
  ToNumber (Mid ({Projects.Actual_StartDate}, 
                 InStr ({Projects.Actual_StartDate}, "/")+1, 
                 InStrRev({Projects.Actual_StartDate},"/")-InStr({Projects.Actual_StartDate}, "/")-1))
 ) 

 >= 

Date (ToNumber (Right ({?actualStart}, 4)),
  ToNumber (Left ({?actualStart}, InStr ({?actualStart}, "/")-1)),
  ToNumber (Mid ({?actualStart}, 
                 InStr ({?actualStart}, "/")+1, 
                 InStrRev({?actualStart},"/")-InStr({?actualStart}, "/")-1))
 )

this is my code upon filter button click

    private void Filter_Btn_Click(object sender, EventArgs e)
    {
        ReportDocument cryRpt = new ReportDocument();
        TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
        TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
        ConnectionInfo crConnectionInfo = new ConnectionInfo();

        cryRpt.Load("D:\\MY_THESIS\\WORKING FILES\\WindowsFormsApplication2\\WindowsFormsApplication2\\Reports\\Crystal Reports\\UsersReport.rpt");
        crConnectionInfo.ServerName = "RITZEL-PC\\SQLEXPRESS";
        crConnectionInfo.UserID = "NNIT-Admin";
        crConnectionInfo.Password = "password";
        crConnectionInfo.DatabaseName = "NNIT DB";

        Tables CrTables = cryRpt.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
        {
            crtableLogoninfo = CrTable.LogOnInfo;
            crtableLogoninfo.ConnectionInfo = crConnectionInfo;
            CrTable.ApplyLogOnInfo(crtableLogoninfo);
        }

        // Create parameter objects
        ParameterFields myParams = new ParameterFields();

        //PARAMETER NAME
        ParameterField myParam = new ParameterField();
        ParameterDiscreteValue myDiscreteValue = new ParameterDiscreteValue();
        myParam.ParameterFieldName = "actualStart";

        myDiscreteValue = new ParameterDiscreteValue();
        myDiscreteValue.Value = FromCreated_DTime.Value.ToShortDateString();
        myParam.CurrentValues.Add(myDiscreteValue);
        myParams.Add(myParam);

        crystalReportViewer1.ParameterFieldInfo = myParams;
        crystalReportViewer1.ReportSource = cryRpt;
    }

Why are you entering your actualStart parameter as a string? Why not enter it as a Crystal date parameter? — user359040
@MarkBannister because if I change it into a date parameter I get an error in my formula editor saying a string is required here (highlighting actualStart). Btw, thanks for answering back. I really appreciate it :) — Ritzel
Do you mean in the selection formula editor? If so, I meant to change the string parameter to a date parameter, and replace all of the >= Date (... expression with a simple >= {?actualStart}. — user359040
@MarkBannister yes I get what you are trying to do. Converting the string to Date. That is why I made actualStart a string and also because its value will be a string value from the datetime picker. Did I understand you correctly? — Ritzel
I think you have misunderstood me - the database field has to be converted to a date from a string, because it is not stored as a date in the database. Given that the actualStart parameter is part of the report, there is no reason to declare it as a string and then convert it to be a date, if it can be created as a date parameter in the first place. I have updated my answer accordingly. Also, did you notice the comment I left against my answer? — user359040

findcaiyzh findcaiyzh · Accepted Answer · 2012-02-16T23:38:18

Depends on the format of your date field in database . here is an example:

stringvar yyear; 
stringvar mmonth; 
stringvar dday; 

dday := {ORDERS.ORDER_DATE}[5 to 6]; 
mmonth := {ORDERS.ORDER_DATE}[3 to 4]; 
yyear := {ORDERS.ORDER_DATE}[1 to 2]; 

if yyear < "50" then 
    date(tonumber(yyear)+2000,tonumber(mmonth),tonumber(dday)) 
else 
    date(tonumber(yyear)+1900,tonumber(mmonth),tonumber(dday))

Above code comes from: Convert date from string to date format

Crystal Reports.Net Invalid Parameter - String to date

this is my code upon filter button click

4 Answers