Comparing Date / DateTime ine Entity SQL Where clause

Question

I have a method which accepts an ObjectQuery and adds Where clauses to it depending on whether it receives values for various filter parameters. The method has to return an ObjectQuery. How can i do a basic Date comparison e.g. where dateX < dateY.

This is my current code:

if (myDateFilter != null)
{
    query = query.Where(
        string.Format(
            "it.ExpireDate < cast('{0}' as System.DateTime)",
            myDateFilter));
}

With myDateFilter having a value of DateTime.Now, this code says: System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

All the other paramaters are string or integer filters so weren't a problem to implement. Any ideas??

OK, it looks like im trying to pass a date which is taking the string form of '31/7/2009' which breaks, but if i pass '1/1/2009' it works, so i think there's a difference in the cultures of whats in the db and whats going on in the application. How can i compare them in the same format????? Or in some culture invariant way... — andrej351, Jul 31 '09 at 06:30

score 0 · Accepted Answer · answered Jul 31 '09 at 21:59

You will have to know the format that the user input the data. Take a look at the DateTime.Parse and ParseExact functions.

http://msdn.microsoft.com/en-us/library/w2sa9yss.aspx

You can use them to parse any input string to a valid datetime if you know the format. There are some culture invariants as well.

A good protection against this is to always format the date from a DateTimePicker, etc to use the long format ( YYYYMMDD HH:MM:SS ). There is a format string to tell .Net to do that, but I can't remember it right now.

If you are accepting the strings from user input you will have to take the CurrentCulture into account (same link above has help).

Comparing Date / DateTime ine Entity SQL Where clause

1 Answers1