how to write a find query with condition for date with M/D/YYYY format and stored as string

Question

I have two fields in my collection like below

{
    "StartDate" : "1/2/2019",
    "EndDate" : "5/14/2019"
}

i want to write a find query to get the documents with in current date example db.collection.find({StartDate:{$lte: '5/9/2019'},EndDate:{$gte:'5/10/2019'}})

but here '5/14/2019' >= '5/9/2019' this condition is returning false '5/14/2019' >= '5/9/2019' this is returning true

I don't understand why it is happening I cannot use MM/DD/YY format I have to stick to M/D/YYYY format

please provide me with a better solution thanks in advance

Possible duplicate of [return query based on date](https://stackoverflow.com/questions/8835757/return-query-based-on-date) — Fraction, May 09 '19 at 11:49
Are `"StartDate"` and `"EndDate"` stored as [ISODate](https://www.compose.com/articles/understanding-dates-in-compose-mongodb/) objects or as simple strings? In the first case, this question is indeed a duplicate. — Tomasz Kasperczyk, May 09 '19 at 11:58
The only way to reliably compare dates formatted as strings is if they are formatted as `YYYY/MM/DD`, anything else won't be consistent. You'll probably have to parse the dates first before comparing. — phuzi, May 09 '19 at 12:13

Tomasz Kasperczyk · Answer 1 · 2019-05-09T13:07:59.323

As clarified in the comments, both dates are simple strings. In order to use logical operators on them, you have to first convert them to ISODate objects by using the aggregation pipeline like so:

db.collection.aggregate([{
    "$addFields": {
        "StartDateISO": {
            "$dateFromString": {
                "dateString": "$StartDate",
                "format": "%m/%d/%Y"
            },
        },
        "EndDateISO": {
            "$dateFromString": {
                "dateString": "$EndDate",
                "format": "%m/%d/%Y"
            }
        },
        "queryStartDateISO": {
            "$dateFromString": {
                "dateString": "5/9/2019",
                "format": "%m/%d/%Y"
            }
        },
        "queryEndDateISO": {
            "$dateFromString": {
                "dateString": "5/10/2019",
                "format": "%m/%d/%Y"
            }
        },
    }
}, {
    "$match": {
        "$expr": {
            "$and": [
                {"$lte": ["$StartDateISO", "$queryStartDateISO"]},
                {"$gte": ["$EndDateISO", "$queryEndDateISO"]}
            ]
        }
    }
}])

It will work in MongoDB 4.0 and newer. The dates were parsed correctly in my tests:

"StartDate": "1/3/2019" became "StartDateISO": "2019-01-03 00:00:00.000Z"

The string dates in the database are parsed correctly because I specified their format. There's still a problem with `new Date("5/10/2019")}` - it's not always consistent. Do you also need to stick to the m/d/Y format in the search query? If so, I'll try to come up with a solution to parse these dates as well. — Tomasz Kasperczyk, May 09 '19 at 12:47
I updated the code - now all dates are parsed (both in the query and in the database) — Tomasz Kasperczyk, May 09 '19 at 13:09

how to write a find query with condition for date with M/D/YYYY format and stored as string

1 Answers1