C # datatable select statement with dates

Question

I am trying to make a select statement in a datatable to get a string that is in the date range that I am looking for. I am new to this and I don't understand how this select statement works. I tried to write this, but it does not work. Could you give me a hand here. I am stuck

foundRows = dt.Select("DATE1 <= '" + date1+ "' AND DATE2 >= '" + date1+ '"'); 

Answer 1

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

Answer 2

These are the best optimal search criteria that I tested. you have dates.

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

and your column in the DataTable that you are applying for. (in my code is 'date')

Your expression of choice will be like this.

  DataRow[] rows = newTable.Select("date >= #" + from_date + "# AND date <= #" + to_date + "#"); 

Answer 3

Using this inside an SSIS script component. I just used the example above that included a “#” around dates. I also converted each to a string. This worked great.

Just in case, you want to know how I configure it inside SSIS: First there was a data stream using the destination of the recordset with the variable Object to store the recordset.

in my script, I included the variable as 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() { base.PreExecute(); a.Fill(AwardedVacTable, Variables.rsAwardedVac); ... ... 

then in the user method datatable data access ...

 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); 

Answer 4

expression = "Date> # 2015-1-1 #"; DataRow [] foundRows = table.Select (expression); 与 select * from tablename, where Date> '2015-1-1'

Answer 5

I posted an answer to this post.

DataTable Selecting the exact DateTime

You can use a similar approach using the checkboxes to select a range.