Cast String to Date in MongoDB query

Question

I have a collection with dates in string type, sush as:

{
 dateTime: "2015-25-15T10:10:10+02:00"
}
{
 dateTime: "2015-25-15T20:11:11+01:00"
}
...

and I need to manth dateTime by UTC Date in aggregation. I did find any way to cast string to date in query.

new ISODate(...) works just with literals

$where - is not applicable in aggregation

Is it possbile?

You could try convert the dates first to ISODate object as in this [**answer**](http://stackoverflow.com/a/16919043). — chridam, Jun 11 '15 at 13:29
nope, because the result of aggregation are more then 16MB, that's why I need casting to write appropriate condition. — Aleksandr Zhytnikov, Jun 11 '15 at 13:40
What exactly are you trying to aggregate on? You really need to "fix your dates" which is the real answer in all string cases. You seem to be trying to avoid it. The real problem here when trying to avoid it is that the dates appear to be UTC. This is really much easier if you just fix the dates to proper objects. — , Jun 11 '15 at 13:42
Brief answer: "No it is not possible to cast a string to a date or any other type in the aggregation framework, So fix them in your data first". Long answer: "Explain yourself first" — , Jun 11 '15 at 13:44
Date type doesn't contain timeZone, this information are valuable.That's why converting all fields to Date not proper way.I'm looking for the way to write condition in aggregation where I can compare String value with date in ISO8601 format to Date.. — Aleksandr Zhytnikov, Jun 11 '15 at 13:54
See that "+1:00" and "+2:00" stuff there. That indicates a different TimeZone for each entry. Please stop arguing and just explain what your aggregation intent here is and "why oh why" can you just not convert the dates in the collection. Please, pretty please with sugar on top. — , Jun 11 '15 at 13:58
I need the aggregation because the collection much complex than in my example. dateTime fields are deeply in document. BSON Date doesn't contain timeZone, and when I convert String "2015-25-15T10:10:10+02:00" to Date, I got UTC Date, and timeZone is lost. — Aleksandr Zhytnikov, Jun 11 '15 at 14:25

score 0 · Answer 1 · answered Apr 18 '23 at 16:59

With MongoDB v4.0+, you can use $toDate to convert your string fields to proper date objects.

With MongoDB v4.2+, you can put it in an update with aggregation pipeline.

db.collection.update({},
[
  {
    $set: {
      dateTime: {
        $toDate: "$dateTime"
      }
    }
  }
])

Mongo Playground

Note: There is invalid month 25 in your sample data. You may want to fix your datasource as commented by @user3561036.

Cast String to Date in MongoDB query

1 Answers1