Trying to convert string data type to date

db.Acc_Detail.updateMany({},[{$set :{date :{$convert:{input :“$mod_typestamp”, to :“date”}}}}])
MongoServerError: Error parsing date string ‘2017-02-14:13:13:51’; 10: Unexpected character ‘:’

Is there any way to format it ?

Hello @Kingshuk_Modak, Welcome to the MongoDB community forum,

The : between date and hour is not valid so $convert or $toDate operators don’t understand the date string,

You can use $dateFromString operator, where you can specify your date format,

db.Acc_Detail.updateMany(
  {},
  [
    {
      $set: {
        date: {
          $dateFromString: {
            dateString: "$mod_typestamp",
            format: "%Y-%m-%d:%H:%M:%S",
            // timezone: "America/New_York" // update to your specific timeozne if you needed
          }
        }
      }
    }
  ]
)

Hi @turivishal I tried that as well however the date is coming as null. Since the format of the string is somewhat different.

mod_typestamp field is given as “2017-02-14:13:11:15” in the below format.

Is there any other way to update it in date format in bulk.?

The code shared by turivisal works really well on a date string field named mod_typestamp and formatted

If you get null, then may be your field is not named mod_typestamp or is not in the format you shared.

1 Like

Hi @steevej and @turivishal All sorted. however the date format has some additional spaces which was creating the problem. Thanks a lot for the help.

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.