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
– sjahan, Commented Oct 2, 2020 at 8:59
Check this out: 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
– sjahan, Commented Oct 2, 2020 at 9:02
yes, but i have more than 3000 document with this format "01/12/2020" so i can't update them all. — Souhir kammoun
– Souhir kammoun, Commented Oct 2, 2020 at 9:02
the problem is that the format in my system is String not a date so the example is not helping me — Souhir kammoun
– Souhir kammoun, Commented Oct 2, 2020 at 9: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
– sjahan, Commented Oct 2, 2020 at 9:04

turivishal · Accepted Answer · 2020-10-13 09:52:59Z

0

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.

answered Oct 13, 2020 at 9:52

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter Data between two String

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related