convert a string to datetime in LinqToEntities (inside the query)

Question

convert a string to datetime in LinqToEntities (inside the query)

c# entity-framework linq

Question

I have a date in the string format in a table and I need to compare it with the parameter to retrieve all rows that match with the given date. how can I do this? I can't use DateTime.Parse and Convert.ToDateTime. both of them are not supported (I get an error). Is this possible at all in Linq OR do I have to write a stored procedure for it due to this limitation?

Note: I can't change the datatype of the column from varchar to DateTime, because that column contains other values as well, not just dates. it is a generic table and the column may contain different types of values based on the values in other columns. changing this design is outside the scope of this question.

sample code ("x" is the date parameter passed into this method):

    from t1 in Table1
    where EntityFunctions.DiffDays(DateTime.Parse(t1.Value),x) == 0
    select new {t1.Col1, t1.Col2};

Update: if I use DateTime.Parse, I get the error "LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression." similar error for Convert.ToDateTime as well.

RKP

Popular Answer

This works. You need an Extensionmethod to make the dateTime parsing safe. After that you can use the result of that method in the Linq query. It will fetch all rows from the table so performance wise this might be a less optimal (!) solution. It answers the question though.

    void Main()
    {
         var stringDates = new List<string> { "2011-13-01", "2011-01-12" };

         DateTime paramDate = new DateTime(2011,01,13);

         var q = from stringDate in stringDates
            let realdate = stringDate.SafeParse()
            where realdate == paramDate
            select new { stringDate, realdate };

         q.Dump();
    }


    static class StringDateParseExt
    {
       public static DateTime SafeParse(this string  any)
        {
          DateTime parsedDate;
          DateTime.TryParseExact(any, 
                "yyyy-dd-MM", 
                System.Globalization.CultureInfo.InvariantCulture , 
                System.Globalization.DateTimeStyles.None, 
                out parsedDate);
          return parsedDate;
        }
    }

rene

View more on Stack Overflow

Douglas · Accepted Answer · 1/31/2012 8:04:15 PM

(Replicating my comment to paste some sample code)

If the date is in string format, couldnâ€™t you apply a ToString on your DateTime (presumably x) and then do a string comparison?

Since you are working on string representations, you need to take care of several issues that would otherwise be handled transparently by DateTime, including:

Date-time format discrepancies (dd/MM/yyyy vs MM/dd/yyyy).
Presence or absence of leading zeros for single-digit days and months (e.g. 01/01/2011 vs 1/1/2001).
Two-digit or four-digit representation of years (e.g. 01/01/2011 vs 01/01/11).
Timezone offsets. For example, the date for 2011-01-01Â 23:30Â -01:00 would actually be 2011-01-02.

The sample code below will work if all your dates are in US format, with two-digit days and months, four-digit years, and no timezone offsets.

from t1 in Table1
where t1.Value.StartsWith(x.ToString(@"MM\/dd\/yyyy"))
select new {t1.Col1, t1.Col2};

Edit: Alternate solution:

If you can define a view in your database, you can circumvent the problem by casting your VARCHAR to DATETIME selectively. Iâ€™m assuming that Value is the name of your date column.

SELECT CONVERT(DATE, Value, 101) AS Value, Col1, Col2
FROM Table1
WHERE ISDATE(Value) = 1

Then, in your LINQ, do a simple DateTime equality check:

from t1 in Table1
where t1.Value == x.Date
select new {t1.Col1, t1.Col2};

convert a string to datetime in LinqToEntities (inside the query)

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related