CODEWITHSUNDEEP
c#asp.netlinq
BUMA
BUMA

Reputation: 235

Filter data with date that is stored as string instead of DateTime

i have two fields in a which will input start date and end date, so i want to filter the data according to the dates entered and display only data in those date ranges, my database the date field is stored as a string not DateTime so iam struggling with writing the query in Linq to filter the data. so far i search i check if the two fields are not null then i'm struggling with query

 if (search.StartDate == null && search.EndDate == null)
            if (search.StartDate!= null && search.EndDate != null)
            {
                    var startDate = search.StartDate;
                    var endDate = search.EndDate;

                    query = query.Where(d => (d.Date) >= startDate.Date && (d.Date) <= endDate.Date).AsQuerybable();
            }

Upvotes: 1

Views: 1185

Answers (1)

Steve Harris
Steve Harris

Reputation: 5109

As your dates are stored MM.DD.YYYY you will have an issue writing these queries as in a string comparison:

12.03.1964 > 04.06.2017

So Ideally your would need to change the date formats throughout your DB to either a Date, or at least to a YYYY-MM-DD format so that a meaningful range can be applied server side.

The second best solution would be to create a view or stored procedure in your database to return records from the table applying a range on the dates so that the range can be applied server side.

The solution that requires no change to your database can only apply the date range client side and involves getting the individual parts of the string together in the correct order:

query = query.Where(d => 
    DateInRangeFormat(d.Date) >= DateInRangeFormat(startDate.Date)
    && DateInRangeFormat(d.Date) <= DateInRangeFormat(endDate.Date))
    .AsQuerybable();

// accepts date in MM.DD.YYYY format and returns YYYYMMDD
public static string DateInRangeFormat(string date)
{
    return date.SubString(6) + date.SubString(0, 2) + date.SubString(3, 2);
}

Upvotes: 1

Related Questions