DateTime in entity Framework && DateTime in sql format returning different results c# entity-framework sql sql-server


Guys i have a big problem in entity framework and DateTime. Here the problem:

First with entity:

When the user submit the form them those values are being create for a condicional query depending on which parameter the user type. Example:

var query =  from user in UserTable

select user;

        query = query.where(add=>add.Category == Request.Form["Category"])

//and here the between 
         if(Request.Form["start_date"] && Request.Form["end_date"])
        query = query.where(add=>add.Date < Request.Form["start_date"] && add.Date > Request.Form["end_date"] )//equivalent to between
    return query.ToList();

Good, but the problem remains that when the user wants to search by date, when converting the request form to datetime then the format are different from the input what was recived:


result : 02/11/2003(not iqual to the user input)

So, there is not coincidence(Well, thats what i guest because I read that entity solved this by itself). And i cannot set Date propery to String Because i cannot use between with a string.

Here the model simplified:

public string Category {get;set;}
public DateTime Date{get;set;} 

But Thats not all

Part 2

I tried to query in 02/11/2003 format to see if the query works and it did correctly, but when i tried with 2003-12-02, here the results

 02/12/2003 format
16330 rows

2003-12-02 format
136 rows

Notice that they are the same but return different results. Moreover, when i check the results, i noticed that when querying in 02/12/2003 format the results include date lesser or highter:


Select date FROM Table WHERE date BETWEEN 02/12/2003 AND  03/12/2003

results include: 05/12/2003,30/12/2003

Why this significant diffents bettwen those two queries?

4/24/2013 4:00:30 PM

Accepted Answer

This has to do with the culture of your database. You can check your current culture by executing:

SELECT @@language

To see the date formats for your culture, execute:

EXEC sp_helplanguage

Based on that, you can Convert.ToDateTime() in C# with your specific culture like so:


DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MaxValue;

// Set this to your culture if it's not en-US
DateTimeFormatInfo usDtfi = new CultureInfo("en-US", false).DateTimeFormat;

try { startDate = Convert.ToDateTime(Request.Form["start_date"], usDtfi); }
catch {}

try { endDate = Convert.ToDateTime(Request.Form["end_date"], usDtfi); }
catch {}

if (startDate != DateTime.MinValue && endDate != DateTime.MaxValue)
    query = query.where(add=>add.Date <= endDate && add.Date >= startDate ) //equivalent to between

Note that you had your dates backwards. You have < startDate and > endDate. Which is the opposite of BETWEEN. (Unless you are considering startDate to be the larger of the 2 dates, which I am not.)

4/24/2013 4:30:59 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow