Checking date between with or

[Thavarajan_M]

Hi good evening Folks, i have a document with two date one i invoice date, order date
now i need to check either one of the provided date falls under provided interval i need to display the results

[
  {
    invoice_id: "INV0010",
    amount: 1000,
    invoice_date: new Date("2023-10-10"),
    order_date: new Date("2023-09-20")
  },
  {
    invoice_id: "INV0011",
    amount: 1000,
    invoice_date: new Date("2023-10-10"),
    order_date: new Date("2023-09-20")
  },
  {
    invoice_id: "INV0012",
    amount: 1000,
    invoice_date: new Date("2023-10-10"),
    order_date: new Date("2023-09-20")
  }
]

and my query is as follows

db.collection.aggregate([
  {
    "$match": {
      "$or": [
        {
          "invoice_date": {
            $gte: {
              $todate: "2023-10-01"
            },
            $lt: {
              $todate: "2023-10-11"
            }
          }
        },
        {
          "order_date": {
            $gte: {
              $todate: "2023-10-01"
            },
            $lt: {
              $todate: "2023-10-11"
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      _id: 1,
      invoice_id: 1,
      amount: 1
    }
  }
])

what am i doing wrong here, though i am having result in the document but i didn’t get any result

[steevej (Steeve Juneau)]

Is there any reasons why you are using $todate rather than new Date() in your query?

Most likely, it is because of a typo in $todate because it should be $toDate as documented.

One way to debug is to simplify. Start by just checking if the invoice_date part works. Then check the order_date part works.

This being said, your query seems to work with new Date().