Take the 2-minute tour ×

i am trying to make a select statement on a datatable to get the row that is within the date range i am looking for. I am new to this an i dont quite understand how this select statement works. I tried to write this but is not working. Can you please give me a hand here. I am stuck

foundRows = dt.Select("DATE1 <= '" + date1+ "' AND DATE2 >= '" + date1+ '"');
share|improve this question
Please work on you accept rate if you want people to answer you question –  Arion Jun 7 '12 at 15:14
you need to accept some answers. –  Jeremy Holovacs Jun 7 '12 at 15:14
add comment

4 Answers

up vote 4 down vote accepted

Besides wrapping your dates with #, if date1 is a DateTime and not a string, you need to use the ToString(your date format) to get the correct sql statement. For debugging it make it easier if first you create a string containing your filter, then do the select using that string. Then you can look at the string and use that in the query builder to validate your sql.

share|improve this answer
ok i got it. string testing = date1.ToString("MM/dd/yyyy"); DataRow[] foundRows; foundRows = dt.Select("DATE1 <= '#" + testing+ "#' AND DATE2 >= '#" + testing+ "#'"); decimal value1 = System.Convert.ToDecimal(foundRows[0][0]); –  George Georgiou Jun 7 '12 at 15:59
what happens in the case that the select doesnt contain anythin. ex. the date is outside of the boundaries? –  George Georgiou Jun 7 '12 at 16:00
add comment

This the Best Optimal Search Criteria I have Tested. you having to dates.

From_Date = 12/01/2012 To_Date = 12/31/2012

and Your column in DataTable upon which you applying . (in my code 'date')

Your Select Statement will be like this.

  DataRow[] rows = newTable.Select("date >= #" + from_date + "# AND date <= #" + to_date + "#");
share|improve this answer
add comment

Using this inside an SSIS script component. I just used the example from above that included "#" around the dates. Also I converted each to string. This worked perfectly.

Just in case you want to know how I setup this up inside SSIS: First had a data flow using the recordset destination with an Object variable to store the recordset.

in my script I included the variable as a read only.

In the main class...

public class ScriptMain : UserComponent

OleDbDataAdapter a = new OleDbDataAdapter();
System.Data.DataTable AwardedVacTable = new System.Data.DataTable();

then in Pre-Execute...

public override void PreExecute()

    a.Fill(AwardedVacTable, Variables.rsAwardedVac);

then in a custom method accessed the datatable ...

String dtFilter = "EmployeeID = " + empId.ToString() + " AND (#" + Convert.ToString(StartDate) "# <= EndDate AND #" + Convert.ToString(StartDate) + "# >= StartDate" + " OR #" + Convert.ToString(StartDate.AddDays((double)numDays)) + "# >= StartDate AND #" + Convert.ToString(StartDate.AddDays((double)numDays)) + "# <= EndDate)";

DataRow[] Overlaps = AwardedVacTable.Select(dtFilter);
share|improve this answer
add comment

Your Answer


