Query MongoDB with custom date String - javascript

Question

I am new to NodeJS. I have a collection in MongoDB which holds the Data shown below

[{
        _id: new ObjectId("6180c67a9b414de991a24c43"),
        cusDate: '20/11/2021 03:32 AM',
        cusName: 'Akila',
        cusEmail: 'akila@gmail.com',
        __v: 0
    },
    {
        _id: new ObjectId("6180c67a9bt14de991a24c43"),
        cusDate: '02/08/2021 08:12 AM',
        cusName: 'Booth',
        cusEmail: 'booth@gmail.com',
        __v: 0
    },
    {
        _id: new ObjectId("u180c67a9b414de991a24c43"),
        cusDate: '12/07/2020 11:38 AM',
        cusName: 'Sam',
        cusEmail: 'sam@gmail.com',
        __v: 0
    }, {
        _id: new ObjectId("61y0c67a9b414de991a24c43"),
        cusDate: '22/01/2021 07:42 AM',
        cusName: 'Ram',
        cusEmail: 'ram@gmail.com',
        __v: 0
    },
]

I want to find and get the Data from Collection based on cusDate.

fromCusDate: 01/06/2020 12:00 AM
toCusDate: 01/03/2021 12:00 PM

Required Output:

[
    {
        _id: new ObjectId("u180c67a9b414de991a24c43"),
        cusDate: '12/07/2020 11:38 AM',
        cusName: 'Sam',
        cusEmail: 'sam@gmail.com',
        __v: 0
    }, {
        _id: new ObjectId("61y0c67a9b414de991a24c43"),
        cusDate: '22/01/2021 07:42 AM',
        cusName: 'Ram',
        cusEmail: 'ram@gmail.com',
        __v: 0
    },
]

My cusDate was in String datatype. The String Date Format was DD/MM/YYYY hh:mm A

Here's the Code i tried,

let getOrdersData = await collection.find({ "cusDate": { "$gte": "01/06/2020 12:00 AM", "$lt": "01/03/2021 12:00 PM" } })

But my date was in String Datatype in collection. so i can't get that data.

I don't know to how to get that data. I want to know the Mongoose query to get these data.

let getOrdersMonth = await collection.find({ "cusDate": { "$gte": "01/06/2020 12:00 AM", "$lt": "01/03/2021 12:00 PM" } })

I found this answer. stackoverflow answer. But it is a mongodb query. I need a mongoose query. I searched stackoverflow but can't find any answers. Please Help me with some solutions.

Any specific issue you have encountered when you adapt to the mongoose version from the mongodb version answer you found? AFAIK, [convert mongodb native aggregation to mongoose](https://mongoosejs.com/docs/api/aggregate.html#aggregate_Aggregate) should requires little effort. Can you share your current attempt and the specific error you faced so we can have better look? — ray, Nov 09 '21 at 12:49
I need a mongoose query to achieve that. My date was in String type. I tried this. `await Collection.find({ "cusDate": { "$gte": new Date("01/11/2021 12:00 PM") } })`. I got no data. the count was 0. — kicacob, Nov 09 '21 at 12:56
You can find the answer to this question here: https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb — to240, Nov 09 '21 at 13:55
mongoose is just a framework to define data models. It **is** mongodb, the queries are the same - maybe apart from the `await` command. — Wernfried Domscheit, Nov 09 '21 at 14:04
You should **never** store date/time values as string, its a design flaw. Store always proper `Date` objects. According to [$dateFromString](https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/), it looks like "AM/PM" 12-hour format is not supported natively by MongoDB, so you have to use a 3rd party library or write you own complex functions. Maybe try [moment.js](https://momentjs.com/docs/) library, it supports parsing date values from almost any format. — Wernfried Domscheit, Nov 09 '21 at 14:10

Akshay Balwani · Answer 1 · 2021-11-09T13:05:06.917

To solve this using date queries in MongoDB , you first need to have a Date type entry in your MongoDB collection. Something of the sort of createdAt,updatedAt. It's easier to query on dates using Mongo but the same logic doesnt work on strings, it has to be a Date type.

So in your model you can define another entry say newCusDate where you take the cusDate and convert it into a date using new Date(cusDate). But this needs to happen when defining the collection. And then you have bunch of queries you can use on Date using MongoDB.

let gtDate = new Date('some date string here');
let ltDate = new Date('some date string here');

let getOrdersMonth = await collection.find({
  "newCusDate": {
    "$gte": gtDate,
    "$lt": ltDate
  }
})

All of them being date objects(Data Type) is very important.

Correct statement, but not really an answer to the question. — Wernfried Domscheit, Nov 09 '21 at 14:12

Query MongoDB with custom date String - javascript

1 Answers1