Filter between to dates

Working with Data Drivers
, ,
1

Hello,

Im using mongoDb 1.32.6 and i had build a c# app in .net 6.0 to retrieve documents from MongoD.

I particular i have a simply mask for ui and i receive startDate and endDate in my api.

My schema is

Account:
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public DateTime SubscriptionDate { get; set; }

in my db i have an account with:

SubscriptionDate : “2022-01-15T00:00:00.000”

Searching this item using filter mask from Compass returning me correctly this one.

but if i create a filter with StartDate and EndDate passed from ui , i dont have anything returned.

my c# code is:

var sendDateFrom = DateTime.ParseExact(DateFrom, "yyyy-MM-ddTHH:mm:ss.fff",CultureInfo.InvariantCulture);
filterDateFrom = Builders<Account>.Filter.Gte(x => x.SubscriptionDate, sendDateFrom);
var sendDateTo = DateTime.ParseExact(DateTo, "yyyy-MM-ddTHH:mm:ss.fff", CultureInfo.InvariantCulture);
filterDateTo = filterDateFrom & Builders<Account>.Filter.Lte(x => x.SubscriptionDate, DateTo);

items = await AccountCollection.Find(filterDateTo.ToList();

I send from ui :

DateFrom=2022-01-01T00:00:00.000

DateTo=2022-01-31T00:00:00.000

But item is never returned.

Thaks for support.

2

Hi, @M_P1,

Welcome to the MongoDB Community Forums. I understand that you’re having trouble filtering by dates with the .NET/C# Driver. You mention that you have an account with:

SubscriptionDate : “2022-01-15T00:00:00.000”

This data indicates that the date is stored as a string and not a BSON datetime type. Since you passed in .NET DateTime structs to your query, these would be rendered in the MQL as BSON datetime types, not strings.

If you have the flexibility to change your schema I would recommend storing the dates as BSON datetime. This can be accomplished using an insert such as the following in the mongosh shell:

db.coll.insertOne({SubscriptionDate: ISODate("2022-01-15T00:00:00.000Z"")})

Alternatively you can store the data using your C# application and the object model that you’ve defined. As long as your property is of type DateTime, it will be stored as a BSON datetime in the database.

There are more advanced options such as using $dateFromString in an aggregation pipeline to transform and query dates stored as strings.

Hopefully this resolves your issue.

Sincerely,
James

1 Like