Convert dates from String format to Date format in mongodb

Question

I have my birthDate in string format like this "2010-03-22". I want to convert it in the Date type in MongoDB.

What db.patient.update() function should I write ? I want to calculate the age of each person.

I used the solution give on How do I convert a property in MongoDB from text to date type? but all the dates got converted to "1970-01-01T00:00:00.000Z".

Answer 1

db.collection.find().forEach(function(e){
e.fieldname = new Date(e.fieldname)
db.collection.save(e)
});

If you are using robomonogo use new ISODate instead of new Date

Answer 2

One approach you could take in converting the field to the correct date object would be by splitting the string on the given delimiter "-". Use parseInt() to convert the delimited strings into numbers, and the new Date() constructor builds a Date from those parts: the first part will be the year, the second part the month, and the last part the day. Since Date uses zero-based month numbers you have to subtract one from the month number.

The following demonstrates this approach:

var cursor = db.patient.find({"birthDate": {"$exists": true, "$type": 2 }}); 
while (cursor.hasNext()) { 
    var doc = cursor.next(); 
    var parts = doc.birthDate.split("-");
    var dt = new Date(
                parseInt(parts[0], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[2], 10) // day
            );
    db.patient.update(
        {"_id": doc._id}, 
        {"$set": {"birthDate": dt}}
    ) 
};

---

For improved performance especially when dealing with large collections, take advantage of using the Bulk API for bulk updates as you will be sending the operations to the server in batches of say 500 which gives you a better performance as you are not sending every request to the server, just once in every 500 requests.

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing the OrderDate fields to date fields:

var bulk = db.patient.initializeUnorderedBulkOp(),
    counter = 0;

db.patient.find({"birthDate": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var parts = doc.birthDate.split("-");
    var dt = new Date(
                parseInt(parts[0], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[2], 10) // day
            );
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "birthDate": dt}
    });

    counter++;
    if (counter % 500 == 0) {
        bulk.execute(); // Execute per 500 operations and re-initialize every 500 update statements
        bulk = db.patient.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 500 != 0) { bulk.execute(); }

---

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite():

var bulkOps = db.patient.find({"birthDate": {"$exists": true, "$type": 2 }}).map(function (doc) { 
    var parts = doc.birthDate.split("-");
    var dt = new Date(
                parseInt(parts[0], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[2], 10) // day
            );
    return { 
        "updateOne": { 
            "filter": { "_id": doc._id } ,              
            "update": { "$set": { "birthDate": dt } } 
        }         
    };          
})

db.patient.bulkWrite(bulkOps);