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.
(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:
dd/MM/yyyy
vs MM/dd/yyyy
).01/01/2011
vs 1/1/2001
).01/01/2011
vs 01/01/11
).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};
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;
}
}