Filter Data between two String

Question

I have a creation date attribute of a document which has a format like this: "05/03/2020" of type String.

I must extract all the documents from my system having a creation date before "05/03/2020".

I tried with:

db.MyCollection.find ({DateC: {$lte: "05/03/2020"}})

but it still returns 0.

Is there a way to compare if a date format String is before another date?

Thank you

I'd suggest you to find out which format you must use to query your DB. Then, you map your input format to your DB format. `05/03/2020` is very unlikely to be ready to be usable in a query. To perform date format, you can use moment.js, which is a pretty good lib to handle dates. — sjahan, Oct 02 '20 at 08:59
Check this out: https://stackoverflow.com/a/8835850/5860648 I think it is a good example of how to query based on a date. Then, if your input is not in the proper format, format it the way you need :) — sjahan, Oct 02 '20 at 09:02
yes, but i have more than 3000 document with this format "01/12/2020" so i can't update them all. — Souhir kammoun, Oct 02 '20 at 09:02
the problem is that the format in my system is String not a date so the example is not helping me — Souhir kammoun, Oct 02 '20 at 09:03
Storing a date as a string is not a good practice, exactly because you won't be able to use date comparison operators further. If I were you, I would first probably plan a migration to fix your data in the DB first. Otherwise, you will have to fetch the whole DB in memory to perform the check programmatically, which you don't want. Plan a migration and fix your DB data to use it properly afterwards! — sjahan, Oct 02 '20 at 09:04

score 0 · Accepted Answer · answered Oct 13 '20 at 09:52

You can use $dateFromString operator to convert $DateC to ISODate,

Make sure your input should be in ISODate format,

db.MyCollection.find([
  {
    $expr: {
      $lte: [
        {
          $dateFromString: {
            dateString: "$DateC",
            format: "%d/%m/%Y"
          }
        },
        ISODate("2020-03-05T00:00:00.000Z")
      ]
    }
  }
])

Playground

I am not sure with your date format, I have predicted as d/m/y, you can manage it your self if its different.

Filter Data between two String

1 Answers1