MongoDB Find If Date Today Falls Between Two Columns-CodePudding

I'm getting unexpected results for getting the collections between two columns. I have this simple collection:

{
  UserID: "1",
  StartDate: "2021-04-15",
  EndDate: "2022-01-18"
}

{
  UserID: "1",
  StartDate: "2022-04-01",
  EndDate: "2022-04-30"
}

Now, I want to filter the result by the current date. Today is 2022-04-01 so the expected result would be only:

{
  UserID: "1",
  StartDate: "2022-04-01",
  EndDate: "2022-04-30"
}

Using this filter is not working as expected:

{ 
  UserID: "1",
  StartDate: {
    "$gte": '2022-04-01',
  }, 
  EndDate: {
    "$lte": '2022-04-01',
  }
}

As I'm getting no result. Using only EndDate $lte filter is not working properly. Need inputs. Thanks.

CodePudding user response：

db.collection.find({
  UserID: "1",
  StartDate: {
    "$lte": "2022-04-01",
    
  },
  EndDate: {
    "$gte": "2022-04-01"
  }
})