MongoTemplate - Find data between two dates not working

I have data in below format:

{
  "_id": {
    "$oid": "669e141c218c685d8483e0c8"
  },
  "artifact": "dummy data for development",
  "createTime": "7-11-2024 07:48:48"
}

I want to find data between two dates and below query is getting generated by MongoTemplate:

{ "createTime" : { "$gte" : "07-01-2024", "$lte" : "07-24-2024"}}

Java code:

@Autowired
MongoTemplate mongoTemplate;

Query query = new Query();
SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy")
query.addCriteria(Criteria.where("createTime").gte(sdf.format(startDate)).lte(sdf.format(endDate)));

List<Entity> result = mongoTemplate.find(query, Entity.class);

I am getting the empty result and no exception is thrown.

If I run:

{ "createTime" : { "$gte" : "07-01-2024", "$lte" : "07-24-2024"}} 

query on Mongo Compass, I get no result.

Any help is appreciated.

Wouldn't recommend storing the Date value as (formatted) string. This makes difficulties in query when you are fetching the document based on date range. Besides, it will not utilize the index. You should revise it and make the change if it is possible.

As the query below, you need to take additional steps to convert the string into Date by format with the $dateFromString operator. This operator is an aggregation operator, hence you need to work with the $expr operator.

db.collection.find({
  $expr: {
    $and: [
      {
        $gte: [
          {
            $dateFromString: {
              dateString: "$createTime",
              format: "%m-%d-%Y %H:%M:%S"
            }
          },
          {
            $toDate: "2024-07-01T00:00:00Z"
          }
        ]
      },
      {
        $lte: [
          {
            $dateFromString: {
              dateString: "$createTime",
              format: "%m-%d-%Y %H:%M:%S"
            }
          },
          {
            $toDate: "2024-07-24T00:00:00Z"
          }
        ]
      }
    ]
  }
})

Demo @ Mongo Playground

Not a MongoTemplate Spring Data user, but you should able to use the raw query with BasicQuery according to the documentation here.

SimpleDateFormat isoDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
String startDateString = isoDateFormat.format(startDate);
String endDateString = isoDateFormat.format(endDate);

String json = String.format(
    "{ $expr: { $and: [ { $gte: [ { $dateFromString: { dateString: \"$createTime\", format: \"%%m-%%d-%%Y %%H:%%M:%%S\" } }, { $toDate: \"%s\" } ] }, { $lte: [ { $dateFromString: { dateString: \"$createTime\", format: \"%%m-%%d-%%Y %%H:%%M:%%S\" } }, { $toDate: \"%s\" } ] } ] } }",
    startDateString, endDateString
);

BasicQuery query = new BasicQuery(json);
List<Entity> result = mongoTemplate.find(query, Entity.class);