CODEWITHSUNDEEP
c#sqlasp.net-mvcdatedatetime
Cole Perry
Cole Perry

Reputation: 160

ASP.NET MVC Filtering results by date returns 0 results

I am trying to filter the results of a database query by date. The user will input a date value as a string and then I use that string to compare to the date of every query result to try to find a match, however there is never a match, even when I know one exists.

Query and filtering:

var documents = from s in db.Documents
                            select s;

if (!String.IsNullOrEmpty(searchString))
{
    documents = documents.Where(s => s.Order_Date.ToString().Contains(searchString) || 
                                     s.Comment.Contains(searchString));
}

It should be noted that if the searchString is found in the Comment column, then it works fine. But again, there is never a match for date.

In the SQL table that the app connects to the column Order_Date is of date datatype (not datetime). However in the model Order_Date is a DateTime variable because as far as I'm aware C# does not have just date.

Here is an example of the problem:

example

Result

result

What am I doing wrong?

Upvotes: 1

Views: 2532

Answers (2)

Cole Perry
Cole Perry

Reputation: 160

I figured it out using Jonathan's comment. This is the simplest way to do it:

if (!String.IsNullOrEmpty(searchString))
{
    try
    {
        var test = DateTime.Parse(searchString);
        documents = documents.Where(s => s.Order_Date == test);
    }
    catch (FormatException e)
    {
        documents = documents.Where(s => s.Comment.Contains(searchString));
    }
}

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

You are comparing 11/8/2004 with s.Order_Date.ToString(). This approach has several problems:

  • Maybe s.Order_Date contains 2004-08-11 but when you do s.Order_Date.ToString() it turns to month-day-year date format 8/11/2004 (instead day-month-year) and 8/11/2004 != 11/8/2004
  • What happens if user enters 11/08/2004 ? 11/08/2004 != 11/8/2004. User will don't understand why they are no results.

If you want to search by date the best solution is to use a date entry control. If for your UX is important to enter date in a text control instead a date control then you should to tokenize text and try to identify dates on text, convert to date and use a date to compare on linq expression.


DateTime? search_date_start = tokenize_and_extract_date_start(searchString)
DateTime? search_date_end = tokenize_and_extract_date_end(searchString)
String? search_comment = remove_dates_from_search_string(searchString)

documents = 
    documents
    .Where(s => 
       search_date_start == null || 
       s.Order_Date >= search_date_start) 
    )
    .Where(s => 
       search_date_end == null || 
       s.Order_Date <= search_date_end) 
    )
    .Where(s => 
       search_comment == null ||
       s.Comment.Contains(search_comment)
    );

Upvotes: 1

Related Questions