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
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
See Converting string to date in mongodb for details of how to fix your data — Neil Lunn

Samip Suwal Samip Suwal · Accepted Answer · 2017-06-02T19:38:11

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);
});

Can't convert from BSON type string to Date

1 Answers