Database
[{_id:ObjectId("64e44ca88b7dc087ced85f3d"),effectiveFrom:ISODate("2023-09-22T00:00:00.000+00:00"),effectiveTo:ISODate("2023-12-30T18:30:00.000+00:00"),createdDate:ISODate("2022-12-31T00:00:00.000+00:00")},{_id:ObjectId("64e44ca88b7dc087ced85f3e"),effectiveFrom:ISODate("2023-09-23T00:00:00.000+00:00"),effectiveTo:ISODate("2023-12-30T18:30:00.000+00:00"),createdDate:ISODate("2022-12-31T00:00:00.000+00:00")}]
Query
db.collection.aggregate([{$addFields:{formattedDate:{$dateTrunc:{date:"$$NOW",unit:"day"}}}},{$match:{$expr:{$and:[{$lte:["$effectiveFrom","$formattedDate"]},{$lt:["$createdDate","$formattedDate"]}]}}}])