C# DataView Date Range with LIKE Operator?!?

Question

I have an XML file:

<SMS>
    <Number>+447761692278</Number>
    <DateTime>2009-07-27T15:20:32</DateTime>
    <Message>Yes</Message>
    <FollowedUpBy>Unassigned</FollowedUpBy>
    <Outcome></Outcome>
    <Quantity>0</Quantity>
    <Points>0</Points>
  </SMS>
  <SMS>
    <Number>+447706583066</Number>
    <DateTime>2009-07-27T15:19:16</DateTime>
    <Message>STOP</Message>
    <FollowedUpBy>Unassigned</FollowedUpBy>
    <Outcome></Outcome>
    <Quantity>0</Quantity>
    <Points>0</Points>
  </SMS>

I use an XMLReader to read the file into a dataset and display it in a datagridview. I want to be able to specify a range of dates to display the data for. For example where the element contains a date between INSERT DATE & INSERT DATE. In order to do this I am using a DATAVIEW and then populating the datagridview with the dataview instead of the dataset.

Currently I have a method as below:

public void DisplayRecSmsByDateRange(string date1,string date2, string readDir)
     {
      DataSet ds = new DataSet("SMS DataSet");
      XmlDataDocument xmlDatadoc = new XmlDataDocument();
      xmlDatadoc.DataSet.ReadXml(readDir);
      ds = xmlDatadoc.DataSet;

      DataView custDV = new DataView(ds.Tables["SMS"]);
      custDV.RowFilter = String.Format("(DateTime >= DateTime LIKE '{0}*' AND DateTime <= DateTime LIKE '{1}*')", EscapeLikeValue(date1), EscapeLikeValue(date2));
      this.dataGridView1.DataSource = custDV;
     }

The problem is that if you look at the xml file, the element contains the time as well as the date. Since I am not interested in the time part of this element, I would use the "LIKE" statement to display the data from the xml file based on just the date part of the element. As a result of this, when i try and perform a boolean operation to say for example - "Show me the data for dates between 2009-07-27 and 2009-07-30", I get an error since the compiler does not like that I am trying to combine the LIKE operator with the boolean <=,>= operators (Shown in the above method). Is there a way around this? I need to be able to display data between a range of dates but use the LIKE operator to search based only the first part of the element.

Help appreciated greatly,

Kind regards.

Answer 1

I'll assume that you can modify/replace the EscapeLikeValue() method so that it returns only date part, e.g. "2009-09-27". This way you can rewrite the filter to something like:

custDV.RowFilter = String.Format("(DateTime >= '{0}' AND DateTime <='{1}')", EscapeLikeValue(date1) + "T00:00:00", EscapeLikeValue(date2) + "T23:59:59");

HTH, Dejan

Answer 2

One way you can do this is to convert the date string parameters into actual datetime objects like so

 DateTime dt = System.Convert.ToDateTime(date1);
 DateTime dt2 = System.Convert.ToDateTime(date2);
 dt2=dt2.AddDays(1);

and then you can modify your where like so

custDV.RowFilter=String.Format("DateTime>={0} and DateTime<{1}",dt.ToShortDateString(),dt2.ToShortDateString());

Answer 3

I wonder if you can do the following:

custDV.RowFilter = String.Format("(Convert(DateTime,'System.DateTime' >= #{0}# AND Convert(DateTime,'System.DateTime' <= #{1}#)", EscapeLikeValue(date1), EscapeLikeValue(date2));

Answer 4

A slight alteration to the method, removing the LIKE operator worked......

public void DisplayRecSmsByDateRange(string date1,string date2, string readDir)
         {
          DataSet ds = new DataSet("SMS DataSet");
          XmlDataDocument xmlDatadoc = new XmlDataDocument();
          xmlDatadoc.DataSet.ReadXml(readDir);
          ds = xmlDatadoc.DataSet;

          DataView custDV = new DataView(ds.Tables["SMS"]);
          custDV.RowFilter = String.Format("(DateTime >= '{0}*' and DateTime <= '{1}*')", EscapeLikeValue(date1), EscapeLikeValue(date2));

          custDV.Sort = "DateTime";
          this.dataGridView1.DataSource = custDV;
         }