How to convert date stored as string/datetime(both) to only datetime in MongoDB?
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?
$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" }} 
