CODEWITHSUNDEEP
c#winformsselectdatatabledatatables
Paul Alexander
Paul Alexander

Reputation: 2748

C# - DataTable.Select() 'String was not recognized as a valid DateTime'

I'm getting a String was not recognized as a valid DateTime exception when trying to select values from a datatable.

Code:

//get start and end dates from datetime pickers
string startDate = dateTimeStart.Value.ToString("dd/MM/yyyy");
string endDate = dateTimeEnd.Value.ToString("dd/MM/yyyy");

//add picked dates to expression
selectExpression = selectExpression + " AND Date >= #" + startDate + "# AND Date <= #" + endDate + "#";

//copy selected events to the filtered results datatable
dtFiltered = dt.Select(selectExpression).CopyToDataTable();

When I execute the code my expression is: 

"Name = 'Test' AND Event = 'open' AND Date >= #13/03/2017# AND Date <= #13/03/2017#"

So why does it not like this? Any help appreciated! Thanks!

Upvotes: 0

Views: 1280

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Afaik it's #03/13/2017#, so month first, since there is no 13th month you get this exception. 

string startDate = dateTimeStart.Value.ToString("MM/dd/yyyy");
string endDate = dateTimeEnd.Value.ToString("MM/dd/yyyy");

MSDN:

Parsing Literal Expressions: All literal expressions must be expressed in the invariant culture locale.

You could also use LINQ, then you have compile time safety and can use any .NET code:

var query = dt.AsEnumerable()
    .Where(r=> r.Field<DateTime>("Date") >=‌ ​dateTimeStart.Value 
            && r.Field<DateTime>("Date") <= dateTimeEnd.Value); 
if(query.Any())
    dtFiltered = query.CopyToDataTable();

Note: i guess you want to include the whole dateTimeEnd-day. Then you need:

            && r.Field<DateTime>("Date") < dateTimeEnd.Value.Date.AddDays(1));

Upvotes: 4

Related Questions