How to query mongodb with date stored as string [duplicate]

Question

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": "[email protected]",
  "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

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 operator. This operator needs to be used along with the $expr operator. — prasad_
– prasad_, Commented Nov 14, 2020 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
– dharani kumar, Commented Nov 14, 2020 at 16:25

J.F. · Accepted Answer · 2020-11-14 16:46:15Z

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.

edited Nov 14, 2020 at 16:46

answered Nov 14, 2020 at 14:06

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

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

10 Comments

dharani kumar Over a year ago

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

J.F. Over a year ago

Only remove first $match stage. Answer updated.

dharani kumar Over a year ago

Thanks I upvoted your answer. One more thing, I'm using mongoDb in node.js. So how to extract that data from this query?

J.F. Over a year ago

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

dharani kumar Over a year ago

Im getting this error - MongoError: Error parsing date string '23/07/2011'; 0: Unexpected character '2'

|

Collectives™ on Stack Overflow

How to query mongodb with date stored as string [duplicate]

1 Answer 1

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Linked

Related