DateTime in entity Framework && DateTime in sql format returning different results

Question

DateTime in entity Framework && DateTime in sql format returning different results

asp.net-mvc-4 c# entity-framework sql sql-server

Question

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;

        if(Request.Form["Category"])
        {
        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
        }
    until...
    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:

User'input
2003-12-02


Convert.ToDateTime
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:

Example:

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?

user2195741

View more on Stack Overflow

McCee · Accepted Answer · 4/24/2013 4:30:59 PM

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:

UPDATED ANSWER

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

DateTime in entity Framework && DateTime in sql format returning different results

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related