How to convert date stored as string/datetime(both) to only datetime in MongoDB?

Question

I am working on a dataset where a field is stored as either string(%Y-%m-%d) or datetime(ISODate Format ex- 2020-05-16T00:00:00.000+00:00). While fetching the same on Python and using $gte/$lte the string type values are getting excluded from the match.

I have tried converting the field to datetime using $dateFromString but since there are values in datetime format, the following error is thrown: $dateFromString requires that 'dateString' be a string, found: date with value 2019-05-31T00:00:00.000Z

Similarly trying to use $dateToString also throws a similar error because of values stored as string in the same field: can't convert from BSON type string to Date.

Also using $toDate throws the following error, which I have no clue about: Error parsing date string 'Invalid date'; 0: passing a time zone identifier as part of the string is not allowed 'I'; 8: Double timezone specification 'd'; 6: Double timezone specification 'd'.

Can anyone suggest a solution as I can't seem to find anything to handle this particular issue?

Answer 1

$toDate is a convenience wrapper for $convert

You could get convert the strings to dates while leaving any values that don't convert properly, like

{$convert: {
      input: "$fieldname",
      to: "date",
      onError: "$fieldname",
      onNull: "$fieldname"
}}