1

Hi I have a mongodb database with employee collection. The documents inside collection look like this -

{
  "_id": {
    "$oid": "5fac29ace10c301364931902"
  },
  "id": 11,
  "first_name": "Kristi",
  "last_name": "Dorkins",
  "email": "kdorkinsa@livejournal.com",
  "gender": "Female",
  "startDate": "07/04/2012",
  "endDate": "30/03/2019",
  "deviceStatus": true
}

I need the employees whose 'startDate' is greater than a specific date(which I'm passing to server from frontend). The Date format I pass to the server looks like this-

2020-11-14T10:36:21.053Z

Since the date in database is in a string format("11/05/2011") I can't find a way to convert that to actual date object in the query statement.

      let temp = new Date(req.query.startDate);
      dbase.collection("employee").find({new Date(startDate): { $gte: new Date(temp) }})
        .toArray()
        .then((res) => console.log(res));

In the above query I tried creating a date object of the key('startDate') but that's not possible it seems.

When I tried in a different way like this just to test-

      dbase.collection("employee").find({startDate: { $gte: "02/11/2010" }})
        .toArray()
        .then((result) => {
          console.log(result.length);
          res.send(result);
        });

For the above query I'm getting all documents with first two digits of startDate greater than first two digits of the given date(greater than '2' in above case). That is not working either

I have already gone through this thread return query based on date and different other similar questions, but they all have the date key stored in database in an ISO or a different format. Also since I'm new to MongoDB and I tried all possible ways which I know. Kindly help

dharani kumar
  • 117
  • 2
  • 14
  • 1
    You are better off converting the `startDate` to a _date_ object and then compare with the input _date_ object (i.e., new Date()). To convert the string date to a date object use the [$dateFromString](https://docs.mongodb.com/v4.2/reference/operator/aggregation/dateFromString/index.html) operator. This operator needs to be used along with the `$expr` operator. – prasad_ Nov 14 '20 at 14:06
  • @prasad_ I'm getting this error "MongoError: Error parsing date string '23/09/2011'; 0: Unexpected character '2'" I think while using $datefromstring on the string date – dharani kumar Nov 14 '20 at 16:25

1 Answers1

1

You can parse the string to date using $dateFromString

Example here with only one field to read easier.

Mongo query is like this:

db.collection.aggregate([
  {
    "$set": {
      "startDate": {
        "$dateFromString": {
          "dateString": "$startDate",
          "format": "%d/%m/%Y"
        }
      }
    }
  },
  {
    "$match": {
      "startDate": {
        "$gte": yourDate
      }
    }
  }
])

Just use $set to replace the field with the parsed date to ISODate using $dateFromString and then you can $match tose whose startDate is greater (or whatever) your date.

J.F.
  • 13,927
  • 9
  • 27
  • 65
  • Thankyou for answering. But what am I supposed to enter in $match? I'm not looking for 'Id', I'm looking for 'startDate' $gte 'input date'. I'm kinda new to this.. so can u please elaborate – dharani kumar Nov 14 '20 at 15:44
  • Only remove first `$match` stage. Answer updated. – J.F. Nov 14 '20 at 15:49
  • Thanks I upvoted your answer. One more thing, I'm using mongoDb in node.js. So how to extract that data from this query? – dharani kumar Nov 14 '20 at 16:07
  • With node.js I've only used mongo with `mongoose` so I don't know how is the syntaxis but I suposse when you run a query, the object returned is the same as the [example](https://mongoplayground.net/p/M5r-QfILVER). So something like `var find = mongo.find(query); console.log(query.startDate);` should works. But I've not used mongo without `mongoose` in node.js so I really don't know. – J.F. Nov 14 '20 at 16:24
  • Im getting this error - MongoError: Error parsing date string '23/07/2011'; 0: Unexpected character '2' – dharani kumar Nov 14 '20 at 16:26
  • Check the [docs](https://docs.mongodb.com/manual/reference/method/Date/). Date type has to be in a specific way. Maybe you have to use JS functions to parse date to another format. For example, tis format: should `"2011-07-23"` works. – J.F. Nov 14 '20 at 16:30
  • But it works in your mongo playground example? – dharani kumar Nov 14 '20 at 16:32
  • So I have to update my database date entries? Like change the format of date in my documents to "yyyy-mm-dd" format and then use this query? – dharani kumar Nov 14 '20 at 16:37
  • Looking into [docs](https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/) it seems you can add a `format` parameter. Try adding `"format": "%d/%m/%Y"` line. I've updated the answer. – J.F. Nov 14 '20 at 16:44
  • Yes with "format" parameter it's working. Thanks a ton man. Also the input date should be converted to date object "$gte": new Date(yourDate) for node.js code – dharani kumar Nov 14 '20 at 16:54