CODEWITHSUNDEEP
c#
coderwill
coderwill

Reputation: 844

String was not recognized as a valid DateTime in c#?

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.

Upvotes: 1

Views: 5105

Answers (5)

Arie
Arie

Reputation: 5373

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

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

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();

Upvotes: 1

Antonio Avnda&#241;o Duran
Antonio Avnda&#241;o Duran

Reputation: 142

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)

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499790

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.

Upvotes: 5

Murf
Murf

Reputation: 1719

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

Upvotes: 1

Related Questions