MongoDB how to update a date yyyy-m-d to yyyy-mm-dd when its a string type

Question

In my mongoDB i have 10,000s of rows that look like this:

{ "_id" : ObjectId("5a037d23f535be649b3ffb64"), "userName" : "drusso", "PRGroup" : "lbs", "PRType" : "Front squat", "PRValue" : "190", "PRDate" : "2017-6-8" }

But in my query i need to sort the PRDate field, which doesn't work when there are dates with 2 digit months since these are stored as strings. So i need a way to update all the rows to yyyy-mm-dd so the sort query works.

I don't know mongo query structures very well so I'm looking for some help. Thanks mucho gracias in advance.

Answer 1

I tried this in MongoDB shell version v3.4.9, I hope it works for you as well. Just replace so with your collection name.

db.so.find().snapshot().forEach(function(e){

    if(e.PRDate.length < 10)
    {
        var tokens = e.PRDate.split("-");
        if(tokens.length == 3)
        {
            var year = tokens[0];
            var month = tokens[1];
            var day = tokens[2];

            if(month.length == 1)
            {
                month = "0" + month;
            }
            if(day.length == 1)
            {
                day = "0" + day;
            }
            e.PRDate = year + "-" + month + "-" + day;
            db.so.save(e);
        }
    }
});