CODEWITHSUNDEEP
mongodbmongodb-query
soubhagya
soubhagya

Reputation: 806

how to filter databse from mongodb between to string formatted date

[
   { "item": "journal", "qty": 25,"date":"1/1/2016", "status": "A" },
   { "item": "notebook", "qty": 50,"date":"10/1/2016", "status": "A" },
   { "item": "paper", "qty": 100,"date":"20/1/2016", "status": "D" },
   { "item": "planner", "qty": 75,"date":"1/2/2016", "status": "D" },
   { "item": "postcard", "qty": 45,"date":"10/2/2016", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"20/5/2016", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"30/7/2016", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"2/3/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"5/5/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"6/5/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"8/10/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"11/10/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"12/11/2017", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"4/3/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"5/6/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"6/7/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"7/7/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"17/11/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"19/12/2018", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"5/1/2019", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"7/1/2019", "status": "A" },
   { "item": "postcard", "qty": 45,"date":"14/3/2019", "status": "A" }
]

Above Is my database structure.

db.lichi.find({date: {$gte : '1/1/2016', $lt : '1/1/2019'}})

Here is the query i am trying to fetch data from database.

Here in database i have string formatted date. I am tring to fetch using above way that, didn't work.

gave result 

  { "item": "journal", "qty": 25,"date":"1/1/2016", "status": "A" },
  { "item": "notebook", "qty": 50,"date":"10/1/2016", "status": "A" }

Only two documents.

PLease have a look.

Upvotes: 1

Views: 88

Answers (1)

mickl
mickl

Reputation: 49945

Storing dates as string is not the best idea since in case like this you have to compare strings instead of dates. If for some reason you have to keep date as string then you can convert it in your query using $dateFromString and then apply your filtering condition:

db.lichi.aggregate([
    {
        $addFields: {
            date: {
                $dateFromString: {
                    dateString: "$date",
                    format: "%d/%m/%Y"
                }
            }
        }
    },
    {
        $match: {
            date: { $gte: ISODate("2016-01-01T00:00:00Z"), $lt: ISODate("2019-01-01T00:00:00Z") }
        }
    }
])

Upvotes: 1

Related Questions