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