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??

Answer 1

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).

Answer 2

Hi all,

Thanks for your responses...it was actually me being an idiot, and visual studio being annoying!

I had created the .dbml file in a deep sub folder, therefore the class that was created was called something stupid like .folder1.folder2.folder3 etc and in my aspx file I had included that namespace....

Very annoying that visual studio creates these default namespaces!

Thanks again higgsy