CODEWITHSUNDEEP
c#datetimeselectdatatable
George Georgiou
George Georgiou

Reputation: 465

c# datatable select statement with dates

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+ '"');

Upvotes: 8

Views: 80446

Answers (5)

nikoo28
nikoo28

Reputation: 2971

I posted an answer on this post.

DataTable Select by exact DateTime

You can use a similar approach by using ticks to select in a range.

Upvotes: 1

Jianghua Guo
Jianghua Guo

Reputation: 11

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

Upvotes: 0

Tomv
Tomv

Reputation: 51

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()
{
    base.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);

Upvotes: 0

DareDevil
DareDevil

Reputation: 5349

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 + "#");

Upvotes: 14

SASS_Shooter
SASS_Shooter

Reputation: 2216

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.

Upvotes: 10

Related Questions