2

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?

Improve this question
2
  • 2
    Not really an answer but when you are using 02/12/2003 this is translating to February 12th 2003, and 2003-12-02 is translating to December 2nd 2003. So it has to do with your date culture. Commented Apr 24, 2013 at 16:00
  • Upvoted up, You answered one of my question :D. Commented Apr 24, 2013 at 16:02

1 Answer 1

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

When doing what you wrote, this is the result: Convert.ToDateTime("2012-09-11");//its returns 12/09/2012 12:00:00 a.m. But in the field of date, the format is 02-12-2012. How can I change the dateformat and with - and being in dateFormat to make the between statement? Remember that i cannot make a between with string and DateTime (DateTime > String)//error, cannot compare dataTime with string.
Did this answer it for you?
Yea, it did. Sorry for not voting up before :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.