Can't convert from BSON type string to Date

Question

I have data like the following example in my mongoDB (collection: data_extraction_test):

{
    "_id" : ObjectId("4f16fc97d1e2d32371003e27"),
    "date" : "14 Nov 2000 08:22:00 -0800"
}

{
    "_id" : ObjectId("4f16fc97d1e2d32371003e28"),
    "date" : "14 Nov 2000 07:37:00 -0800"
}

{
    "_id" : ObjectId("4f16fc97d1e2d32371003e29"),
    "date" : "14 Nov 2000 07:25:00 -0800"
}

When running the javascript Code (extract is given below) the following error appears: Can't convert from BSON type string to Date

let cursor = col.aggregate([
                        {
                            $project:{
                                _id: "$_id",
                                year: {$year: new Date("13 Nov 2000 01:41:00 -0800 (PST)")},
                                // month: new Date(new String("$date")),
                                month: { $month: "$date" },
                            }
                        },
                        {
                            $out: "dan"
                        }
                    ]).toArray((err, items)=>{
                        assert.equal(null, err);
                        console.log("daniel",items);
                        resolve(true);
                        db.close();
                    });

How can i convert the string into ISODate?

Hi is that the actual representation of the documents in your db? how did u insert those dates into the db? — Samip Suwal, Jun 02 '17 at 19:27
Hi it is the actual representation of the documents in this collection. I've executed this function before: db.messages.aggregate([{$project : {date: {$substr : ["$headers.Date", 5, 26]}}},{$out: "date_extraction_test"}]) — js_coder, Jun 02 '17 at 19:42
See [Converting string to date in mongodb](https://stackoverflow.com/questions/10942931/converting-string-to-date-in-mongodb) for details of how to fix your data — Neil Lunn, Jun 02 '17 at 21:35

Samip Suwal · Accepted Answer · 2017-06-03T12:40:17.197

The issue is that you are trying to convert RFC date format as string object. And the query is trying to convert assuming its a Date object.

I took the dates in your database, replaced them with ISO 8601 format.

"14 Nov 2000 08:22:00 -0800" => ISODate("2000-11-14T16:22:00.000Z")

"14 Nov 2000 07:37:00 -0800" => ISODate("2000-11-14T15:37:00Z")

"14 Nov 2000 07:25:00 -0800" => ISODate("2000-11-14T15:25:00Z")

After which the aggregation query worked.

Please note that the according to the doc. The dates are stored as a 64 bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970).

It might be better to store the dates as Date object rather than string to begin with. Is there a reason that you are storing them as strings?

Update As from the link suggested by the user Neil Lunn. You can use following script which will convert the property to ISO date.

(Please make backup of your db. Incase something doesn't go right)

//change test to your collection name 
db.test.find({}).forEach(function (doc) {
    doc.date = new Date(doc.date);
    db.test.save(doc);
});

Thanks for your answer. How you replaced them with ISO 8601 format? Have you edited all this manually? I have a lot of data and can not edit it all manually ... The dates are stored as string in my mongoDB, because the dates in the original dataset are formated as string. — js_coder, Jun 03 '17 at 07:53

Can't convert from BSON type string to Date

1 Answers1