Mongodb query to change the date data type from string to ISO date type . Using update many

$dateFromString and $toDate both will stop the operation upon the first error, when it fails to convert the given string into a date object. This is what happens in this case. Request you may first assess the data, and take a call on how to deal with the erroneous documents. The following sample documents and action log may give more details on it.

Sample documents

xyz> use test
test> t = db.test;
test> t.drop()
test> t.find();
[
  {
    stringdate: '06-15-2008'
  },
  {
    stringdate: 'oct 20 2020'
  },
  {
    stringdate: '2017-02-08'
  },
  {
    stringdate: '2017-02-08T12:10:40.787'
  },
  {
    stringdate: '2017-02-08T12:10:40.787'
  }
]

Action log

First of all, check for the erroneous documents. Please note that the following query does not stop on error. This is achieved by OnError. It means on failing to convert a given document, the value given on OnError will be provided. This is useful to identify the erroneous documents. In the sample case, there is one such document. This query filters only such documents and displays it with _id and the old date value.

test> t.aggregate([{ $project: { newdate: { $dateFromString: { dateString: '$stringdate', onError:'error' } },stringdate:1}},{$match:{newdate:'error'}}]);
[
  {
    _id: ObjectId('6641ad7451d50517dda0deea'),
    stringdate: '06-15-2008',
    newdate: 'error'
  }
]

The above query results will be helpful to assess the documents. And take a call on how to deal with it. In the sample case, there is only one non-convertible document. This shall be corrected by the following query.

t.update({stringdate:'06-15-2008'}, { $set:{stringdate:'2008-06-15'}});

Now when we run the query in step 1, it will not yield any documents. As an optional check, the following query can also be run. It would count the number of types in this key and show the count against each type. Ideally it should produce only date type and the count should be as per the documents in the collection. This makes sure that the conversion process is complete and the key has only date type values.

t.aggregate([{$group:{_id:{$type:"$stringdate"},count:{$sum:1}}}]);
[ { _id: 'date', count: 5 } ]

Request you may assess your collection based on the output of such a query discussed above. And see how many documents are failing to convert. By seeing the volume, you will be able to take a call whether a manual correction is possible or not. Once such documents are corrected, your first query itself will be sufficient to complete the job. Please note the following modification in it, $toDate and $dateFromString essentially do the same, therefore only one would be enough.

db.collection.updateMany( { QCDate: { $exists: true } }, [ { $set: { QCDate: { $dateFromString: { dateString: "$QCDate" } }  } } ] )

Note: In case there are many records found for correction, it may require further analysis on the erroneous documents to achieve the possible automation. However, the basic nature of the process required remains the same – data correction.