DataTable.Select filterexpression giving unexpected results RRS feed

  • Question

  • I have a datatable that contains (simplified example)... PERSON_ID, START_DATE, END_DATE, JOB_START_DATE, JOB_END_DATE.

    I carry out the following action on the datatable.

    dt.Select(string.Format("PERSON_ID = {0} AND START_DATE <= '{1}' AND (END_DATE >=  '{1}' OR END_DATE IS NULL) AND JOB_START_DATE <= '{1}' AND (JOB_END_DATE >=  '{1}' OR JOB_END_DATE IS NULL)", "12345", DateTime.Now.Date));

    i.e. I want to find a specific person, but only if they are a current employee and have a current job.

    The problem is that I am getting results through for rows where JOB_END_DATE is in the past. The filterexpression syntax that I have used is similar syntax that I would use for SQL, but is it valid for the filterexpression here. It would appear not, or am I missing something...

    • Moved by SamAgain Thursday, July 8, 2010 3:38 AM better fit (From:.NET Base Class Library)
    • Moved by VMazurModerator Thursday, July 8, 2010 10:27 AM (From:ADO.NET Managed Providers)
    Wednesday, July 7, 2010 10:45 AM


All replies

  • make sure the date formating is correctly, I think it's the problem.

    Wednesday, July 7, 2010 11:25 AM
  • Thanks for the reply Maher.

    Your comments pointed me in the right direction.

    School boy error actually, the date columns in my datatable were set to String instead of DateTime. Because I have 50+ datatables in my dataset this one was missed off!

    Panic over!!!!

    Wednesday, July 7, 2010 12:43 PM