1

I have written this code for a date range filter in c# using linq but am getting an error on the server. On my local machine, the code is working very well but when deployed on the server it is getting an error.

This is my code:

string fromDate = "15-03-2017";
string toDate = "17-03-2017";
DateTime FromDate = Convert.ToDateTime(fromDate);
DateTime ToDate = Convert.ToDateTime(toDate);                        
UserList = db.Users
    .Where(t => DbFunctions.TruncateTime(t.datetime) >= FromDate 
             && DbFunctions.TruncateTime(t.datetime) <= ToDate)
    .OrderByDescending(a => a.datetime)
    .ToList();

I don't know where my mistake is in this query. If anyone knows then please let me know how to resolve this issue. On my local machine the code is working very well but on server it is getting an error.

Improve this question
13
  • It's more likely that your regional settings are not the same as on your server... so three ways to fix it. 1 => Change servers settings ... 2 change web.config. 3 => write decent conversion code :) Commented Mar 15, 2017 at 8:56
  • Use the Convert.ToDateTime(string,IFormatProvider) overload. The server culture is different from your local machine. You're lucky you get the error instead of the server silently misreading month as day and vice versa. Commented Mar 15, 2017 at 8:58
  • 1
    The issue has nothing in common with LINQ, please remove the linq tag. Commented Mar 15, 2017 at 9:01
  • guys still issue is come i have try this all wave after.any other wave?? Commented Mar 15, 2017 at 9:15
  • 2
    @Hiren: It's very hard for people to help you when you don't answer the questions asked in comments. That format ("MM/DD/YYYY") is not the same as the format you've specified in the question, and it's not clear what you mean by "set this format" either. Commented Mar 15, 2017 at 11:11

5 Answers 5

Reset to default
5

You're using a method that depends on the current thread's current culture - and the system time zone.

You'd be much better off using DateTime.ParseExact in my view, and potentially specifying a DateTimeStyles value to force the use of UTC. Simple parsing:

// Names changed to follow .NET naming conventions
CultureInfo invariantCulture = CultureInfo.InvariantCulture;
DateTime fromDateTime = DateTime.ParseExact(fromDate, "dd-MM-yyyy", invariantCulture);
DateTime toDateTime = DateTime.ParseExact(toDate, "dd-MM-yyyy", invariantCulture);

Specifying the invariant culture means that you won't accidentally end up parsing the date as if it's specified in a non-Gregorian calendar.

Note that this is an unusual format to start with - if you can use an ISO-8601 format, that would be better IMO.

I'd also suggest that if you're doing significant amounts of date/time work, you consider my Noda Time project, which makes life simpler in terms of not worrying about the time zone of a date, because it's just a date... you'd parse these strings as LocalDate values.

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

6 Comments

may be ParseExact takes 3 arguments. and i m try this wave but still issue is getting.
DateTime.ParseExact need three parameters, right?
@Hiren: Fixed - which addresses a separate issue anyway (with explanation to that effect).
@currarpickt: Done.
@Hiren: And that error is...? (It's really hard to help with insufficient information.)
|
1

I think there are different regions on your server and local. You can fix this by using DateTime.ParseExact. Example given below; edit to your needs

DateTime dt=DateTime.ParseExact("15-03-2017", "dd-MM-yyyy", CultureInfo.InvariantCulture);

That would work on all regions and solves your problem

Improve this answer

Comments

1

Have you tried using CultureInfo? user culture info from server, as example I'm using US culture Info.

DateTimeFormatInfo usCinfo = new CultureInfo("en-US", false).DateTimeFormat;
DateTime fromDate = Convert.ToDateTime("12/01/2011", usCinfo)
Improve this answer

1 Comment

still issue come
1

You either should make sure your string conforms to ISO 8601 format (it has yyyy-MM-dd format for date).

string fromDate = "2017-03-15";
string toDate = "2017-03-17";
DateTime FromDate = Convert.ToDateTime(fromDate);

Or use DateTime.ParseExact to specify format of your string:

string fromDate = "15-03-2017";
string toDate = "17-03-2017";
DateTime FromDate = 
    DateTime.ParseExact(fromDate, "dd-MM-yyyy", CultureInfo.InvariantCulture)

Note that you can avoid using DbFunctions.TruncateTime. First condition checks whether t.datetime is from same day as FromDate or after it:

DbFunctions.TruncateTime(t.datetime) >= FromDate

it will give same results if you will not truncate time. If t.datetime has greater date, then truncating time will not change anything in this condition. If date is same, then truncating time will only change condition result from 'greater' to 'equals'. Second condition is checking that t.datetime is from same day as ToDate or before that day:

DbFunctions.TruncateTime(t.datetime) <= ToDate

You will get same results if you'll check whether t.date is less than day next to ToDate. So query will look like:

DateTime FromDate = Convert.ToDateTime(fromDate);
DateTime ToDate = Convert.ToDateTime(toDate).AddDays(1);                        
UserList = db.Users
     .Where(u => u.datetime >= FromDate && u.datetime < ToDate)
     .OrderByDescending(u => u.datetime)
     .ToList();
Improve this answer

4 Comments

are you saying i am pass this format in DateTime()?
@Hiren either change format of string to year-month-day as I did in first example, or if you cannot change string format, then use second example and specify your format
@Hiren sorry, I don't understand what you are asking. Just copy-paste code from answer. The "dd-MM-yyyy" string is the format
i have try after remove DbFunctions.TruncateTime() this funcation in local working very well but on server error is come
1

Languages on your systems (local and server) are probably different. When you parse dates and numbers, you should provide the format info/culture info/format string explicitly to avoid such problems. There are multiple ways to do this, for example one that will work with your code:

string toDate = "17-03-2017";
DateTimeFormatInfo formatInfo = new DateTimeFormatInfo();
formatInfo.ShortDatePattern = "dd-MM-yyyy";
formatInfo.DateSeparator = "-";
DateTime ToDate = Convert.ToDateTime(toDate, formatInfo);

EDIT: from what you provided in the comments to your question, it probably was the issue of incorrect date separator. This happens on systems with English language, the separator is set to "/" when parsing date strings, no matter what format string is provided. One way to overcome this is to provide dates like this: "03-17-2017" (beware the Month is provide before Day here, format "MM/dd/yyyy" and not "dd/MM/yyyy". Another way is to provide the date separator:

formatInfo.DateSeparator = "-";

If this is not it, then the problem probably has to do with TruncateTime().

Improve this answer

1 Comment

@Hiren what error exactly are you getting? if it's not related to parsing dates, then it may be TruncateTime issue or something else related to the linq query instead

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.