CODEWITHSUNDEEP
c#mongodbdatetime
Rookie
Rookie

Reputation: 351

On searching documents in mongodb collection using ISODate range, I get the error "String was not recognized as a valid DateTime"

I am trying to query MongoDB using the following -

List<BsonDocument> list = NoSqlBusinessEntityBase.LoadByWhereClause("peoplecounting",
    string.Concat("{siteid:\"", siteid, "\", locationid:\"", location._id ,"\",
    StartTime: {$gte:ISODate(\"",old.ToString("yyyy-mm-dd hh:mm:ss"),"\")}, 
    EndTime: {$lte:ISODate(\"",current.ToString("yyyy-MM-dd hh:mm:ss"), "\"\")}}"));

The LoadByWhereClause() function is as follows -

public static List<BsonDocument> LoadDataByWhere(string table, string whereClause)
{
    var collection = db.GetCollection(table);
    QueryDocument whereDoc = new QueryDocument(BsonDocument.Parse(whereClause));
    var resultSet = collection.Find(whereDoc);
    List<BsonDocument> docs = resultSet.ToList();

    if (resultSet.Count() > 0)
    {
        foreach (BsonDocument doc in docs)
        {
            doc.Set("_id", doc.GetElement("_id").ToString().Split('=')[1]);
        }
        return docs;
    }
    else
    {
        return null;
    }
}

Even though the query runs fine in MongoDB console and returns documents

db.peoplecounting.find({siteid:"53f62abf66455068373665ff", locationid:"53f62bb86645506837366603",
    StartTime:{$gte:ISODate("2012-12-03 02:40:00")}, EndTime:{$lte:ISODate("2013-12-03 07:40:00")}}

I get the error when I try to load in C# using the LoadByWhereClause function. The error is String was not recognized as a valid DateTime. while parsing the whereClause. How can I possibly fix this? I am unable to determine what is going wrong here.

Upvotes: 1

Views: 1170

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1503489

It's not entirely clear, but I suspect the problem may well be how you're formatting the date. This:

old.ToString("yyyy-mm-dd hh:mm:ss")

should almost certainly be this:

old.ToString("yyyy-MM-dd HH:mm:ss")

or possibly

old.ToString("yyyy-MM-dd'T'HH:mm:ss")

Because:

  • mm means minutes. You don't want the minutes value between your year and day-of-month; you want the month (MM)
  • hh means "hour of half-day" (i.e. 1-12). You want the hour of full day, 0-23 (HH)
  • ISO-8601 uses a T literal to separate the date frmo the time.

I note that your current.ToString is better, but not correct - it gets the month right, but not the hour. The fact that these are inconsistent is a problem to start with - I would advise you to write a separate method to convert a DateTime appropriately.

Upvotes: 4

Related Questions