How to compare a date field which is stored in string format in mongo db version 3.2?

Question

I have a mongo db field in where customer has stored the date in string format like below. VTimestamp: "3/12/2023, 10:07:12 PM". ( MM/DD/YYYY HH:MM:SS ) So now we have a requirement where we will get two dates & we need to compare those dates with Vtimestamp and send the records matching. Please find the below query where Im passing to model.find(),

  stDt is in the format : 3/8/2023, 12:00:00 AM   ( MM/DD/YYYY HH:MM:SS )
  ltDt is in the format : 3/12/2023, 12:00:00 AM   ( MM/DD/YYYY HH:MM:SS )

query = { VTimestamp: { $gte:  stDt, $lte: ltDt }, VStatus: "A" }

Here everything is in string format instead of date. The above query is not working. I have seen many answers suggesting to use $expr, $addtofield which mongodb 3.2 gives an error unrecognized pipeline and i realized that those are for higher versions. Can any one please let me know how to do this in version 3.2.

 db.collectionName.aggregate([
{
    $addFields: {
        justDate: {
            $VTimestamp: {
                 { "$dateFromString": { "dateString": "$VTimestamp", "format": "%m-%d-%Y" }},
            },
        },
    },
},
{
    $match: {
        justDate: {
            $gte: 3-7-2023,
            $lte: 13-7-2023,
        },
    },
},

]);

The above query is also not working.

My data in database would be in the below format.

  {
    "_id" : ObjectId("640767b1728daa8407f8770d"),
    "E_ID" : "1601596167791988071",
    "Org_Type" : "Public Limited Company",
    "T_ID" : "1607100000000002626",
    "VStatus" : "true",
    "VTimestamp" : "3/12/2023, 10:07:12 PM",
    "VComments" : "bhbhbhbbhbhbhbh"
  },

{
    "_id" : ObjectId("640767b1728dbb8407f9770e"),
    "E_ID" : "1596167791988072",
    "Org_Type" : "Private Limited Company",
    "T_ID" : "1607100000000002627",
    "VStatus" : "true",
    "VTimestamp" : "3/8/2023, 11:03:10 PM",
    "VComments" : "nononnnnnno"
  }

score 0 · Answer 1 · answered Mar 13 '23 at 11:02

0

you just need to change $addFields => $project because it doesnt support yet on v 3.2. reff: list operator agrgation v3.2

db.collectionName.aggregate([
  {
    $project: {
      justDate: { $dateToString: { format: "%m-%d-%Y", date: "$VTimestamp" } },
    },
  },
  {
    $match: {
      justDate: {
        $gte: "3-7-2023",
        $lte: "13-7-2023",
      },
    },
  },
]);

note: i havent tested the code.

answered Mar 13 '23 at 11:02

Tobok Sitanggang

607
5
15

When I try to use like this, getting the below error "{ MongoError: can't convert from BSON type String to Date".. please help. – sasi Mar 14 '23 at 04:54
would you update your post with data from database, so i can do test and put it in [mongo-playground](https://mongoplayground.net/) – Tobok Sitanggang Mar 14 '23 at 05:06
TobokSitanggang I have updated the data format . In some cases VTimestamp field might also not be there.. Could you please check. – sasi Mar 14 '23 at 05:14
1

Hei @sasi, unfortunately, you have to do it manually, there was a discussion about this for [version > 2.6 < 3.2](https://stackoverflow.com/questions/10942931/converting-string-to-date-in-mongodb) and [here](https://stackoverflow.com/questions/55806351/how-to-format-date-in-mongo-db-3-2-0). i suggest you to update all of the documents to use `ISODate` and/or with format `MM/DD/YYYY (01/12/2023)` and dont allow for `M/D/YYYY (1/8/2023)`. if `Vtimestamp` have format `MM/DD/YYYY`, we would able to do it with `[$substr](https://www.mongodb.com/docs/v3.2/reference/operator/aggregation/substr/) – Tobok Sitanggang Mar 14 '23 at 06:25
ok, Thank you for the closure. I will add another field and perform. – sasi Mar 15 '23 at 09:28

How to compare a date field which is stored in string format in mongo db version 3.2?

1 Answers1